﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Text;
using System.IO;
using System.Threading;
using System.Web;
using System.Web.UI;
using KPIS.DBM;
using KPIS.GERP.GHRMS.MODEL;
using KPIS.GERP.GHRMS.IDAL;

namespace KPIS.GERP.GHRMS.DAL
{
    [Serializable]
    public sealed class EmployeeDAL : IEmployeeDAL
    {
        IDBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

        
        public EmployeeDAL()
        {
        }

        public IList<hrm_ps_employees> GetEmployees(hrm_ps_employees empInfo)
        {
            string strSQL;
            
            int typeSeq = empInfo.emp_type_seq == null ? 0 : (int)empInfo.emp_type_seq;
            int empSeq = empInfo.emp_seq == null ? 0 : empInfo.emp_seq;
            int jsSeq = empInfo.working_job_site_seq == null ? 0 : (int)empInfo.working_job_site_seq;

            List<hrm_ps_employees> result = new List<hrm_ps_employees>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Sequence", empSeq);
                dbManager.AddParameters(2, "@typeSeq", typeSeq);
                dbManager.AddParameters(3, "@JobSite", jsSeq);

                strSQL = "SELECT *"                   
                    + " FROM HRM_PS_EMPLOYEES"
                    + " WHERE RECORD_STATUS not like @DeleteRecord"
                    + (empSeq > 0 ? " AND EMP_SEQ = @Sequence" : "")
                    + (typeSeq > 0 ? " AND EMP_TYPE_SEQ = @typeSeq" : "")
                    + (jsSeq > 0 ? " AND WORKING_JOB_SITE_SEQ LIKE @JobSite" : "");

                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                result = (List<hrm_ps_employees>)CollectionHelper.ConvertTo<hrm_ps_employees>(dtb);
                //result.Count > 0 ? result.Count.ToString() : 0;
                //return result;                
            }
            catch (Exception ex)
            {
                return null;
                throw new Exception("class EmployeeDAL:" + ex.Message);
            }
            finally
            {
                //dbManager.CloseReader();
                dbManager.Dispose();
            }
            return result;
        }

        public IList<EmployeeInfo> ListEmployees(EmployeeInfo empInfo)
        {
            string strSQL;
            int jsSeq = 0;
            int yearRetire = 0;
            int yearRetireTo = 0;

            int typeSeq = empInfo.EmployeeType == null ? 0 : empInfo.EmployeeType.Sequence;
            if (empInfo.Position != null)
                jsSeq = empInfo.Position.JobSite == null ? 0 : (int)empInfo.Position.JobSite.Sequence;
            if (empInfo.RetireBudgetYear != null)
                yearRetire = (int)empInfo.RetireBudgetYear;
            if (empInfo.RetireBudgetYearEnd != null)
                yearRetireTo = (int)empInfo.RetireBudgetYearEnd;

            List<EmployeeInfo> infoList = new List<EmployeeInfo>();

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(6);

                dbManager.AddParameters(0, "@DeleteRecord", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@Sequence", empInfo.Sequence);
                dbManager.AddParameters(2, "@typeSeq", typeSeq);
                dbManager.AddParameters(3, "@JobSite", "%/" + jsSeq.ToString() + "/%");
                dbManager.AddParameters(4, "@yearRetire", yearRetire);
                dbManager.AddParameters(5, "@yearRetireTo", yearRetireTo);

                strSQL = "SELECT emp.*"
                        + ", YEAR(emp.RETIRE_DATE) + 543 AS RETIRE_BUDGET_YEAR"
                        + ", CONCAT(age_year, ' ปี ', age_month, ' เดือน ', age_day, ' วัน') AS ADMITTED_DURATION"
                    + " FROM("
                        + " SELECT e.EMP_SEQ, e.NAME_PREFIX_SEQ, e.FIRST_NAME, e.LAST_NAME, e.FIRST_NAME_ENG"
                        + ", e.LAST_NAME_ENG, e.GENDER_SEQ, e.RELIGION_SEQ, e.RACE_NAT_SEQ, e.NAT_SEQ"
                        + ", e.BIRTH_DATE, e.EMP_TYPE_SEQ, e.CLASS_SEQ, e.SALARY_LEVEL_SEQ, e.SALARY, e.POS_SEQ"
                        + ", e.WORKING_JOB_SITE_SEQ, e.DATE_ADMITTED, e.DATE_WORK_START, e.DATE_QUIT, e.EMP_STATUS_SEQ"
                        + ", e.CITIZEN, e.TAX_PAYER , e.SOCIAL, e.OFFICIAL, e.BLOOD_GROUP_SEQ"  //e.EMP_SEC_SEQ, 
                        + ", e.PROBATION_DATE_END, e.DEATH_DATE, e.DATE_LATEST_POS, e.DATE_LATEST_CLASS"
                        + ", e.DATE_LATEST_WORKING_JOB_SITE, e.RECORD_STATUS, e.CREATED_BY, e.UPDATED_BY"   //, e.FUND_MEMBER
                        + ", e.NICK_NAME, YEAR(GETDATE()) - YEAR(e.BIRTH_DATE) AS AGE"
                        + ", case when month(GETDATE()) < month(e.DATE_ADMITTED) "
                            + " then year(GETDATE()) - year(e.DATE_ADMITTED) - 1 "
                            + " else year(GETDATE()) - year(e.DATE_ADMITTED)"
                            + " end as age_year"
                        + ", case when month(GETDATE()) < month(e.DATE_ADMITTED) then"
                            + " case when day(GETDATE()) < day(e.DATE_ADMITTED)"
                                + " then (13 - month(e.DATE_ADMITTED)) + month(GETDATE())"
                                + " else (12 - month(e.DATE_ADMITTED)) + month(GETDATE())"
                                + " end"
                            + " else  month(GETDATE()) - month(e.DATE_ADMITTED)"
                            + " end as age_month"
                        + ", case when month(GETDATE()) < month(e.DATE_ADMITTED) then"
                            + " case when day(GETDATE()) < day(e.DATE_ADMITTED)"
                                + " then (DAY(e.DATE_ADMITTED) - day(e.DATE_ADMITTED)) + day(GETDATE())"
                                + " else day(GETDATE()) - day(e.DATE_ADMITTED)"
                                + " end"
                            + " else  day(GETDATE()) - day(e.DATE_ADMITTED)"
                            + " end as age_day"
                        + ", CASE WHEN MONTH(e.BIRTH_DATE) >= 10 AND DAY(e.BIRTH_DATE) > 1"
                            + " THEN concat( YEAR(e.BIRTH_DATE) + 61, '-', '10-01') "
                            + " ELSE concat( YEAR(e.BIRTH_DATE) + 60, '-', '10-01') "
                            + " END AS RETIRE_DATE"
                        + ", CASE WHEN MONTH(e.BIRTH_DATE) >= 10  AND DAY(e.BIRTH_DATE) > 1 "
                            + " THEN 61 ELSE 60 "
                            + " END AS RETIRE_AGE"
                        + ", CONCAT(np.PREFIX_NAME, e.FIRST_NAME, '  ', e.LAST_NAME) AS FULL_NAME,p.POS_REF"
                    + " FROM HRM_PS_EMPLOYEES e"
                        + " LEFT JOIN HRM_MP_POSITIONS p ON e.POS_SEQ = p.POS_SEQ"
                        + " LEFT JOIN HRM_MP_JOB_SITES j ON p.JOB_SITE_SEQ = j.JOB_SITE_SEQ"
                        + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                    + " WHERE e.RECORD_STATUS not like @DeleteRecord"
                    + (empInfo.Sequence > 0 ? " AND e.EMP_SEQ = @Sequence" : "")
                    + (typeSeq > 0 ? " AND e.EMP_TYPE_SEQ = @typeSeq" : "")
                    + (jsSeq > 0 ? " AND j.JOB_SITE_FULL_PATH LIKE @JobSite" : "")                    
                    //+ " AND p.POS_SEQ IS NOT NULL"                    
                    + ")emp "
                    + " WHERE 0 = 0"
                    + (yearRetire > 0 ? yearRetireTo > 0 ? " AND YEAR(emp.RETIRE_DATE)+ 543 >= @yearRetire AND YEAR(emp.RETIRE_DATE)+ 543 <= @yearRetireTo"
                        : " AND YEAR(emp.RETIRE_DATE)+ 543 = @yearRetire" : "")
                    + " ORDER BY emp.EMP_TYPE_SEQ + 0 desc, emp.POS_REF + 0 asc";

                DataTable dt = dbManager.ExecuteDataTable(CommandType.Text, strSQL);
                IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);
                 
                    MasNamePrefixDAL prefixDAL = new MasNamePrefixDAL();
                    MasNamePrefixInfo prefixInfo = new MasNamePrefixInfo();
                    MasGenderDAL genderDAL = new MasGenderDAL();
                    MasGenderInfo genderInfo = new MasGenderInfo();
                    MasReligionDAL religionDAL = new MasReligionDAL();
                    MasReligionInfo religionInfo = new MasReligionInfo();
                    MasNationalitiesDAL natDAL = new MasNationalitiesDAL();
                    MasNationalitiesInfo racenatInfo = new MasNationalitiesInfo();
                    MasNationalitiesInfo natInfo = new MasNationalitiesInfo();
                    MasBloodGroupDAL boodDAL = new MasBloodGroupDAL();
                    MasBloodGroupInfo boodInfo = new MasBloodGroupInfo();
                    EmployeeTypeDAL etDAL = new EmployeeTypeDAL();
                    EmployeeTypeInfo etInfo = new EmployeeTypeInfo();
                    ClassDAL classDAL = new ClassDAL();
                    ClassInfo classInfo = new ClassInfo();
                    PositionDAL posDAL = new PositionDAL();
                    PositionInfo posInfo = new PositionInfo();
                    JobSiteDAL jsDAL = new JobSiteDAL();
                    JobSiteInfo jsInfo = new JobSiteInfo();
                    EmployeeStatusDAL esDAL = new EmployeeStatusDAL();
                    EmployeeStatusInfo esInfo = new EmployeeStatusInfo();
                    AddressDAL addressDAL = new AddressDAL();
                    AddressInfo addressInfo = new AddressInfo();
                    //IList<MasNamePrefixInfo> prefixList = prefixDAL.ListNamePrefixes(new MasNamePrefixInfo(rdr.GetInt32(1)));
                    //IList<MasGenderInfo> genderList = genderDAL.ListMasGenders(new MasGenderInfo(rdr.GetInt32(6)));
                    //IList<MasReligionInfo> religionList = religionDAL.ListReligions(new MasReligionInfo(rdr.GetInt32(7)));
                    //IList<MasNationalitiesInfo> racenatList = natDAL.ListNationalities(new MasNationalitiesInfo(rdr.GetInt32(8)));
                    //IList<MasNationalitiesInfo> natList = natDAL.ListNationalities(new MasNationalitiesInfo(rdr.GetInt32(9)));
                    //IList<MasBloodGroupInfo> boodList = boodDAL.ListBloodGroups(new MasBloodGroupInfo(rdr.GetInt32(26)));
                    //IList<EmployeeTypeInfo> etList = etDAL.ListEmployeeTypes(new EmployeeTypeInfo(rdr.GetInt32(11)));
                    //IList<ClassInfo> classList = classDAL.ListClasses(new ClassInfo(rdr.GetInt32(12)));
                    //IList<PositionInfo> posList = posDAL.ListPositions(new PositionInfo(rdr.GetInt32(15)));
                    //IList<JobSiteInfo> jsList = jsDAL.ListJobSite(new JobSiteInfo(rdr.GetInt32(16)));
                    //IList<EmployeeStatusInfo> esList = esDAL.LoadListEmployeeStatus(new EmployeeStatusInfo(rdr.GetInt32(20)));

                while (rdr.Read())
                {
                    if (!rdr.IsDBNull(1))
                    {
                        IList<MasNamePrefixInfo> prefixList = prefixDAL.ListNamePrefixes(new MasNamePrefixInfo(rdr.GetInt32(1)));
                        prefixInfo = prefixList.Count > 0 ? prefixList[0] : null;
                    }
                    //****//****//****//****//****//****//****//****//****//****//****//*******
                    if (!rdr.IsDBNull(6))
                    {
                        IList<MasGenderInfo> genderList = genderDAL.ListMasGenders(new MasGenderInfo(rdr.GetInt32(6)));
                        genderInfo = genderList.Count > 0 ? genderList[0] : null;
                    }
                    //****//****//****//****//****//****//****//****//****//****//****//*******
                    if (!rdr.IsDBNull(7))
                    {
                        IList<MasReligionInfo> religionList = religionDAL.ListReligions(new MasReligionInfo(rdr.GetInt32(7)));
                        religionInfo = religionList.Count > 0 ? religionList[0] : null;
                    }
                    //****//****//****//****//****//****//****//****//****//****//****//*******
                    if (!rdr.IsDBNull(8))
                    { 
                        IList<MasNationalitiesInfo> racenatList = natDAL.ListNationalities(new MasNationalitiesInfo(rdr.GetInt32(8)));
                        racenatInfo = racenatList.Count > 0 ? racenatList[0] : null;
                    }
                    //****//****//****//****//****//****//****//****//****//****//****//*******
                    if (!rdr.IsDBNull(9))
                    {
                        IList<MasNationalitiesInfo> natList = natDAL.ListNationalities(new MasNationalitiesInfo(rdr.GetInt32(9)));
                        natInfo = natList.Count > 0 ? natList[0] : null;
                    }
                    //****//****//****//****//****//****//****//****//****//****//****//*******
                    if (!rdr.IsDBNull(26))
                    {
                        IList<MasBloodGroupInfo> boodList = boodDAL.ListBloodGroups(new MasBloodGroupInfo(rdr.GetInt32(26)));
                        boodInfo = boodList.Count > 0 ? boodList[0] : null;
                    }
                    //****//****//****//****//****//****//****//****//****//****//****//*******
                    if (!rdr.IsDBNull(11))
                    {
                        IList<EmployeeTypeInfo> etList = etDAL.ListEmployeeTypes(new EmployeeTypeInfo(rdr.GetInt32(11)));
                        etInfo = etList.Count > 0 ? etList[0] : null;
                    }
                    //****//****//****//****//****//****//****//****//****//****//****//*******
                    if (!rdr.IsDBNull(12))
                    {
                        IList<ClassInfo> classList = classDAL.ListClasses(new ClassInfo(rdr.GetInt32(12)));
                        classInfo = classList.Count > 0 ? classList[0] : null;
                    }
                    //****//****//****//****//****//****//****//****//****//****//****//*******
                    if (!rdr.IsDBNull(15))
                    {
                        IList<PositionInfo> posList = posDAL.ListPositions(new PositionInfo(rdr.GetInt32(15)));
                        posInfo = posList.Count > 0 ? posList[0] : null;
                    }
                    //****//****//****//****//****//****//****//****//****//****//****//*******
                    if (!rdr.IsDBNull(16) && rdr.GetInt32(16) > 0)
                    {
                        IList<JobSiteInfo> jsList = jsDAL.ListJobSite(new JobSiteInfo(rdr.GetInt32(16)));
                        jsInfo = jsList.Count > 0 ? jsList[0] : null;
                    }
                    //****//****//****//****//****//****//****//****//****//****//****//*******
                    if (!rdr.IsDBNull(20))
                    {
                        IList<EmployeeStatusInfo> esList = esDAL.LoadListEmployeeStatus(new EmployeeStatusInfo(rdr.GetInt32(20)));
                        esInfo = esList.Count > 0 ? esList[0] : null;
                    }

                    //IList<AddressInfo> addressList = addressDAL.GetEmployeeAddressInfo(rdr.GetInt32(0), 1);
                    //addressInfo = addressList.Count > 0 ? addressList[0] : null;
                   
                    PersonInfo psInfo = new PersonInfo(rdr.IsDBNull(2) ? "" : rdr.GetString(2)
                                , rdr.IsDBNull(3) ? "" : rdr.GetString(3)
                                , rdr.IsDBNull(4) ? "" : rdr.GetString(4)
                                , rdr.IsDBNull(5) ? "" : rdr.GetString(5)
                                , rdr.IsDBNull(36) ? "" : rdr.GetInt32(36).ToString()
                                , rdr.IsDBNull(22) ? "" : rdr.GetString(22)
                                , rdr.IsDBNull(23) ? "" : rdr.GetString(23)
                                , rdr.IsDBNull(24) ? "" : rdr.GetString(24)
                                , rdr.IsDBNull(25) ? "" : rdr.GetInt32(25).ToString()
                                , prefixInfo
                                , genderInfo
                                , religionInfo
                                , racenatInfo
                                , natInfo
                                , rdr.IsDBNull(10) ? (DateTime?)null : rdr.GetDateTime(10)
                                , boodInfo
                                , rdr.IsDBNull(28) ? (DateTime?)null : rdr.GetDateTime(28));
                    psInfo.NickName = rdr.IsDBNull(36) ? "" : rdr.GetInt32(36).ToString();
                    psInfo.Age = rdr.IsDBNull(35) ? 0 : rdr.GetInt32(35);
                    psInfo.FullName = rdr.IsDBNull(41) ? "" : rdr.GetString(41);
                   

                    EmployeeInfo Info = new EmployeeInfo(rdr.GetInt32(0) 
                            , psInfo    
                            , etInfo
                            , classInfo
                            , rdr.IsDBNull(13) ? (int?)null : rdr.GetInt32(13)
                            , rdr.IsDBNull(14) ? (double?)null : (double?)rdr.GetDecimal(14)
                            , posInfo
                            , jsInfo
                            , rdr.IsDBNull(17) ? (DateTime?)null : rdr.GetDateTime(17)
                            , rdr.IsDBNull(18) ? (DateTime?)null : rdr.GetDateTime(18)
                            , rdr.IsDBNull(19) ? (DateTime?)null : rdr.GetDateTime(19)
                            , esInfo
                            , rdr.IsDBNull(20) ? (int?)null : rdr.GetInt32(20)
                            , rdr.IsDBNull(26) ? (DateTime?)null : rdr.GetDateTime(26)
                            , rdr.IsDBNull(28) ? (DateTime?)null : rdr.GetDateTime(28)
                            , rdr.IsDBNull(29) ? (DateTime?)null : rdr.GetDateTime(29)
                            , rdr.IsDBNull(30) ? (DateTime?)null : rdr.GetDateTime(30)
                            , true
                            , rdr.IsDBNull(31) ? "" : rdr.GetString(31)
                            , rdr.IsDBNull(32) ? (int?)null : rdr.GetInt32(32)
                            , rdr.IsDBNull(33) ? (int?)null : rdr.GetInt32(33));
                    Info.RetireDate = rdr.IsDBNull(39) ? (DateTime?)null : Convert.ToDateTime(rdr.GetString(39));
                    Info.RetireAge = rdr.IsDBNull(40) ? (int?)null : rdr.GetInt32(40);
                    Info.RetireBudgetYear = rdr.IsDBNull(42) ? (int?)null : rdr.GetInt32(42);                    
                    Info.AdmittedDuration = rdr.IsDBNull(43) ? " - " : rdr.GetInt32(43).ToString();
                    Info.Address = addressInfo;
                    
                    infoList.Add(Info);
                  
                }


                //while (rdr.Read())
                //{
                //    MasNamePrefixDAL prefixDAL = new MasNamePrefixDAL();
                //    MasNamePrefixInfo prefixInfo = new MasNamePrefixInfo();
                //    if (!rdr.IsDBNull(1))
                //    {
                //        IList<MasNamePrefixInfo> prefixList = prefixDAL.ListNamePrefixes(new MasNamePrefixInfo(rdr.GetInt32(1)));
                //        prefixInfo = prefixList.Count > 0 ? prefixList[0] : null;
                //    }
                //    //***************************************************
                //    MasGenderDAL genderDAL = new MasGenderDAL();
                //    MasGenderInfo genderInfo = new MasGenderInfo();
                //    if (!rdr.IsDBNull(6))
                //    {
                //        IList<MasGenderInfo> genderList = genderDAL.ListMasGenders(new MasGenderInfo(rdr.GetInt32(6)));
                //        genderInfo = genderList.Count > 0 ? genderList[0] : null;
                //    }
                //    //***************************************************
                //    MasReligionDAL religionDAL = new MasReligionDAL();
                //    MasReligionInfo religionInfo = new MasReligionInfo();
                //    if (!rdr.IsDBNull(7))
                //    {
                //        IList<MasReligionInfo> religionList = religionDAL.ListReligions(new MasReligionInfo(rdr.GetInt32(7)));
                //        religionInfo = religionList.Count > 0 ? religionList[0] : null;
                //    }
                //    //***************************************************
                //    MasNationalitiesDAL natDAL = new MasNationalitiesDAL();
                //    MasNationalitiesInfo racenatInfo = new MasNationalitiesInfo();
                //    MasNationalitiesInfo natInfo = new MasNationalitiesInfo();
                //    if (!rdr.IsDBNull(8))
                //    {
                //        IList<MasNationalitiesInfo> racenatList = natDAL.ListNationalities(new MasNationalitiesInfo(rdr.GetInt32(8)));
                //        racenatInfo = racenatList.Count > 0 ? racenatList[0] : null;
                //    }
                //    //***************************************************
                //    if (!rdr.IsDBNull(9))
                //    {
                //        IList<MasNationalitiesInfo> natList = natDAL.ListNationalities(new MasNationalitiesInfo(rdr.GetInt32(9)));
                //        natInfo = natList.Count > 0 ? natList[0] : null;
                //    }
                //    //***************************************************
                //    MasBloodGroupDAL boodDAL = new MasBloodGroupDAL();
                //    MasBloodGroupInfo boodInfo = new MasBloodGroupInfo();
                //    if (!rdr.IsDBNull(26))
                //    {
                //        IList<MasBloodGroupInfo> boodList = boodDAL.ListBloodGroups(new MasBloodGroupInfo(rdr.GetInt32(26)));
                //        boodInfo = boodList.Count > 0 ? boodList[0] : null;
                //    }
                //    //***************************************************
                //    EmployeeTypeDAL etDAL = new EmployeeTypeDAL();
                //    EmployeeTypeInfo etInfo = new EmployeeTypeInfo();
                //    if (!rdr.IsDBNull(11))
                //    {
                //        IList<EmployeeTypeInfo> etList = etDAL.ListEmployeeTypes(new EmployeeTypeInfo(rdr.GetInt32(11)));
                //        etInfo = etList.Count > 0 ? etList[0] : null;
                //    }
                //    //***************************************************
                //    ClassDAL classDAL = new ClassDAL();
                //    ClassInfo classInfo = new ClassInfo();
                //    if (!rdr.IsDBNull(12))
                //    {
                //        IList<ClassInfo> classList = classDAL.ListClasses(new ClassInfo(rdr.GetInt32(12)));
                //        classInfo = classList.Count > 0 ? classList[0] : null;
                //    }
                //    //***************************************************
                //    PositionDAL posDAL = new PositionDAL();
                //    PositionInfo posInfo = new PositionInfo();
                //    if (!rdr.IsDBNull(15))
                //    {
                //        IList<PositionInfo> posList = posDAL.ListPositions(new PositionInfo(rdr.GetInt32(15)));
                //        posInfo = posList.Count > 0 ? posList[0] : null;
                //    }
                //    //***************************************************
                //    JobSiteDAL jsDAL = new JobSiteDAL();
                //    JobSiteInfo jsInfo = new JobSiteInfo();
                //    if (!rdr.IsDBNull(16) && rdr.GetInt32(16) > 0)
                //    {
                //        IList<JobSiteInfo> jsList = jsDAL.ListJobSite(new JobSiteInfo(rdr.GetInt32(16)));
                //        jsInfo = jsList.Count > 0 ? jsList[0] : null;
                //    }
                //    //***************************************************
                //    EmployeeStatusDAL esDAL = new EmployeeStatusDAL();
                //    EmployeeStatusInfo esInfo = new EmployeeStatusInfo();
                //    if (!rdr.IsDBNull(20))
                //    {
                //        IList<EmployeeStatusInfo> esList = esDAL.LoadListEmployeeStatus(new EmployeeStatusInfo(rdr.GetInt32(20)));
                //        esInfo = esList.Count > 0 ? esList[0] : null;
                //    }
                //    AddressDAL addressDAL = new AddressDAL();
                //    AddressInfo addressInfo = new AddressInfo();
                //    //IList<AddressInfo> addressList = addressDAL.GetEmployeeAddressInfo(rdr.GetInt32(0), 1);
                //    //addressInfo = addressList.Count > 0 ? addressList[0] : null;

                //    PersonInfo psInfo = new PersonInfo(rdr.IsDBNull(2) ? "" : rdr.GetString(2)
                //                , rdr.IsDBNull(3) ? "" : rdr.GetString(3)
                //                , rdr.IsDBNull(4) ? "" : rdr.GetString(4)
                //                , rdr.IsDBNull(5) ? "" : rdr.GetString(5)
                //                , rdr.IsDBNull(36) ? "" : rdr.GetString(36)
                //                , rdr.IsDBNull(22) ? "" : rdr.GetString(22)
                //                , rdr.IsDBNull(23) ? "" : rdr.GetString(23)
                //                , rdr.IsDBNull(24) ? "" : rdr.GetString(24)
                //                , rdr.IsDBNull(25) ? "" : rdr.GetString(25)
                //                , prefixInfo
                //                , genderInfo
                //                , religionInfo
                //                , racenatInfo
                //                , natInfo
                //                , rdr.IsDBNull(10) ? (DateTime?)null : rdr.GetDateTime(10)
                //                , boodInfo
                //                , rdr.IsDBNull(28) ? (DateTime?)null : rdr.GetDateTime(28));
                //    psInfo.NickName = rdr.IsDBNull(36) ? "" : rdr.GetString(36);
                //    psInfo.Age = rdr.IsDBNull(37) ? 0 : rdr.GetInt32(37);
                //    psInfo.FullName = rdr.IsDBNull(43) ? "" : rdr.GetString(43);


                //    EmployeeInfo Info = new EmployeeInfo(rdr.GetInt32(0)
                //            , psInfo
                //            , etInfo
                //            , classInfo
                //            , rdr.IsDBNull(13) ? (int?)null : rdr.GetInt32(13)
                //            , rdr.IsDBNull(14) ? (double?)null : rdr.GetDouble(14)
                //            , posInfo
                //            , jsInfo
                //            , rdr.IsDBNull(17) ? (DateTime?)null : rdr.GetDateTime(17)
                //            , rdr.IsDBNull(18) ? (DateTime?)null : rdr.GetDateTime(18)
                //            , rdr.IsDBNull(19) ? (DateTime?)null : rdr.GetDateTime(19)
                //            , esInfo
                //            , rdr.IsDBNull(21) ? (int?)null : rdr.GetInt32(21)
                //            , rdr.IsDBNull(27) ? (DateTime?)null : rdr.GetDateTime(27)
                //            , rdr.IsDBNull(29) ? (DateTime?)null : rdr.GetDateTime(29)
                //            , rdr.IsDBNull(30) ? (DateTime?)null : rdr.GetDateTime(30)
                //            , rdr.IsDBNull(31) ? (DateTime?)null : rdr.GetDateTime(31)
                //            , true
                //            , rdr.IsDBNull(33) ? "" : rdr.GetString(33)
                //            , rdr.IsDBNull(34) ? (int?)null : rdr.GetInt32(34)
                //            , rdr.IsDBNull(35) ? (int?)null : rdr.GetInt32(35));
                //    Info.RetireDate = rdr.IsDBNull(41) ? (DateTime?)null : rdr.GetDateTime(41);
                //    Info.RetireAge = rdr.IsDBNull(42) ? (int?)null : rdr.GetInt32(42);
                //    Info.RetireBudgetYear = rdr.IsDBNull(44) ? (int?)null : rdr.GetInt32(44);
                //    Info.AdmittedDuration = rdr.IsDBNull(45) ? " - " : rdr.GetString(45);
                //    Info.Address = addressInfo;

                //    infoList.Add(Info);

                //}
            }
            catch (Exception  ex)
            {
                return null;
                throw new Exception("class EmployeeDAL:" + ex.Message);
            }
            finally
            {
                dbManager.CloseReader();
                dbManager.Dispose();
            }
            return infoList;
        }

        public List<hrm_ps_employees> LoadEmployeesLite(bool getInactiveEmployee = false)
        {
            List<hrm_ps_employees> list; 

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);
                dbManager.AddParameters(0, "@delete_status", "D"); 
                dbManager.AddParameters(1, "@active", "1");

                string strSQL = @"select e.*, es.active 
                                 , concat(coalesce(np.prefix_short, np.prefix_name, np.prefix_short_eng, np.prefix_name_eng, '') 
                                 , coalesce(first_name, first_name_eng, ''), ' ', coalesce(last_name, last_name_eng, '')) as emp_name 
                                 , p.pos_ref, p.pos_name, j2.job_site_name  
                                  from hrm_ps_employees e 
                                  left join mas_name_prefixes np on e.name_prefix_seq = np.name_prefix_seq 
                                  left join hrm_ps_employee_statuses es on e.emp_status_seq = es.emp_status_seq 
                                  LEFT JOIN hrm_mp_positions p ON e.pos_seq = p.pos_seq
                                  LEFT JOIN hrm_mp_job_sites j2 ON e.working_job_site_seq = j2.job_site_seq
                                  where e.record_status not like @delete_status "
                                + (!getInactiveEmployee ? " and es.active = @active " : "");
                 
                DataTable dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                list = (List<hrm_ps_employees>)CollectionHelper.ConvertTo<hrm_ps_employees>(dtb);

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null) { dbManager.Connection.Close(); dbManager.Dispose(); }
            }
            return list;
        }

        public DataSet LoadEmployees(Nullable<int> Sequence, string EmpTypeSequence, Nullable<int> JobSiteSequence
            , Nullable<int> WorkingJobSiteSequence, bool bActive, int StartRetire, int EndRetire, string SearchText)
        {
            string strSQL;
            DataSet ds = null;
            
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(9);

                dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@EmpTypeSequence", EmpTypeSequence);
                dbManager.AddParameters(2, "@Active", bActive);
                dbManager.AddParameters(3, "@JobSiteSequence", JobSiteSequence);
                dbManager.AddParameters(4, "@WorkingJobSiteSequence", WorkingJobSiteSequence);
                dbManager.AddParameters(5, "@Sequence", Sequence);
                dbManager.AddParameters(6, "@SearchText", SearchText);
                dbManager.AddParameters(7, "@StartRetire", StartRetire);
                dbManager.AddParameters(8, "@EndRetire", EndRetire);

                strSQL = "SELECT distinct emp.*, emp.FULL_NAME as emp_name FROM("
                        + "SELECT e.EMP_SEQ, e.NAME_PREFIX_SEQ, np.PREFIX_NAME, e.FIRST_NAME, e.LAST_NAME"
                        + ", e.FIRST_NAME_ENG, e.LAST_NAME_ENG, np.PREFIX_NAME_ENG"
                        + ", CONCAT(np.PREFIX_NAME, e.FIRST_NAME, ' ', e.LAST_NAME) AS FULL_NAME"
                        + ", CONCAT(isnull(np.PREFIX_NAME_ENG, ''), e.FIRST_NAME_ENG, ' ', e.LAST_NAME_ENG) AS FULL_NAME_ENG"   // ifnull to isnull
                        + ", e.GENDER_SEQ, e.RELIGION_SEQ, e.RACE_NAT_SEQ, e.NAT_SEQ, e.BIRTH_DATE, e.EMP_TYPE_SEQ"
                        + ", e.CLASS_SEQ, e.SALARY_LEVEL_SEQ, e.SALARY, e.POS_SEQ, e.WORKING_JOB_SITE_SEQ"
                        + ", e.DATE_ADMITTED, e.DATE_WORK_START, e.DATE_QUIT, e.EMP_STATUS_SEQ" //, e.EMP_SEC_SEQ
                        + ", e.CITIZEN, e.TAX_PAYER , e.SOCIAL, e.OFFICIAL, e.BLOOD_GROUP_SEQ, e.PROBATION_DATE_END, e.DEATH_DATE"
                        + ", e.DATE_LATEST_POS, e.DATE_LATEST_CLASS, e.DATE_LATEST_WORKING_JOB_SITE"    //, e.FUND_MEMBER
                        + ", e.MAR_STAT_SEQ"
                        + ", p.POS_REF, p.POS_NAME, p.JOB_SITE_SEQ, pj.JOB_SITE_NAME"
                        + ", c.CLASS_NAME, wj.JOB_SITE_NAME AS WORKING_JOB_SITE_NAME, et.EMP_TYPE_NAME"
                        + ", gen.GENDER_NAME, re.RELIGION_NAME, rnat.NAT_NAME AS RACE_NAT_NAME, nat.NAT_NAME"
                        + ", es.EMP_STATUS_NAME, f.POS_TYPE_SEQ, pt.POS_TYPE_NAME, b.BLOOD_GROUP_NAME, ms.MAR_STAT_NAME"
                        + ", CASE WHEN MONTH(e.BIRTH_DATE) >= 10 AND DAY(e.BIRTH_DATE) > 1"
                            + " THEN CAST(concat( YEAR(e.BIRTH_DATE) + 61, '-', '10-01') AS DATE) "
                            + " ELSE CAST(concat( YEAR(e.BIRTH_DATE) + 60, '-', '10-01') AS DATE) "
                            + " END AS RETIRE_DATE"
                        + ", f.FIELD_POS_NAME, ex.EXEC_POS_NAME, ex.EXEC_POS_SEQ"
                        + ", CONCAT(p.POS_REF, ' - ', p.POS_NAME) AS POS_TEXT"
                        + ", dbo.F_Get_Age(e.DATE_ADMITTED)  AS ADMITTED_COUNT"     //", F_Get_Age(e.DATE_ADMITTED)  AS ADMITTED_COUNT" 
                        + ", dbo.F_Get_Age(e.DATE_LATEST_POS) AS POSITION_COUNT"    //", F_Get_Age(e.DATE_LATEST_POS) AS POSITION_COUNT"
                        // + ", e.CODE_SCAN"
                        // Remark : hrm_ps_employees.image_unique = Unique ID ของ รูปภาพบุคลลากร ในหน้าทะเบียนประวัติ
                        // Ps. เป็น Running Unique เพราะรูปภาพ หากใช้ชื่อเป็น Citizen เวลาเปลี่ยนรูป IE จะจำรูปเดิมและไม่โหลดรูปใหม่
                        + ", e.image_unique"
                        + ", bb.book_seq, bb.bank_acc_no, bb.bank_seq, bb.branch_seq"
                        + ", bank.bank_name, branch.branch_name"
                        + ", concat(coalesce(fnp.prefix_name, ''), coalesce(father.parent_name, ''), ' ', coalesce(father.parent_last_name, '')) as father_name"
                        + ", concat(coalesce(mnp.prefix_name, ''), coalesce(mother.parent_name, ''), ' ', coalesce(mother.parent_last_name, '')) as mother_name"
                        + ", concat(coalesce(snp.prefix_name, ''), coalesce(spouse.spouse_name, ''), ' ', coalesce(spouse.spouse_last_name, '')) as spouse_name"
                        + ", spouse.place_of_birth"
                   + " FROM HRM_PS_EMPLOYEES e"
                        + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                        + " LEFT JOIN HRM_PS_EMPLOYEE_TYPES et ON e.EMP_TYPE_SEQ = et.EMP_TYPE_SEQ"
                        + " LEFT JOIN HRM_MP_CLASSES c ON e.CLASS_SEQ = c.CLASS_SEQ"
                        + " LEFT JOIN HRM_MP_POSITIONS p ON e.POS_SEQ = p.POS_SEQ"
                        + " LEFT JOIN HRM_MP_FIELD_POSITIONS f ON p.FIELD_POS_SEQ = f.FIELD_POS_SEQ"
                        + " LEFT JOIN HRM_MP_POSITION_TYPES pt ON f.POS_TYPE_SEQ = pt.POS_TYPE_SEQ"
                        + " LEFT JOIN HRM_MP_EXEC_POSITIONS ex ON p.EXEC_POS_SEQ = ex.EXEC_POS_SEQ"
                        + " LEFT JOIN HRM_MP_JOB_SITES pj ON p.JOB_SITE_SEQ = pj.JOB_SITE_SEQ"
                        + " LEFT JOIN HRM_MP_JOB_SITES wj ON e.WORKING_JOB_SITE_SEQ = wj.JOB_SITE_SEQ"
                        + " LEFT JOIN HRM_PS_EMPLOYEE_STATUSES es ON e.EMP_STATUS_SEQ = es.EMP_STATUS_SEQ"
                        + " LEFT JOIN HRM_PS_EMP_BOOK_BANKS bb ON e.EMP_SEQ = bb.EMP_SEQ"
                        + " LEFT JOIN FIM_MAS_BANKS bank ON bb.bank_seq = bank.bank_seq"
                        + " LEFT JOIN FIM_MAS_BANK_BRANCHS branch ON bb.branch_seq = branch.branch_seq"
                        + " LEFT JOIN MAS_GENDERS gen ON e.GENDER_SEQ = gen.GENDER_SEQ"
                        + " LEFT JOIN MAS_RELIGIONS re ON e.RELIGION_SEQ = re.RELIGION_SEQ"
                        + " LEFT JOIN MAS_NATIONALITIES rnat ON e.RACE_NAT_SEQ = rnat.NAT_SEQ"
                        + " LEFT JOIN MAS_NATIONALITIES nat ON e.NAT_SEQ = nat.NAT_SEQ"
                        + " LEFT JOIN MAS_BLOOD_GROUPS b ON e.BLOOD_GROUP_SEQ = b.BLOOD_GROUP_SEQ"
                        + " LEFT JOIN MAS_MARITAL_STATUSES ms ON e.MAR_STAT_SEQ = ms.MAR_STAT_SEQ"
                        + " left join hrm_ps_emp_parents father on e.emp_seq = father.emp_seq"
	                        + " and father.parent_type = 1"
	                        + " and father.record_status not like 'D'"
                        + " left join hrm_ps_emp_parents mother on e.emp_seq = mother.emp_seq"
	                        + " and mother.parent_type = 2"
	                        + " and mother.record_status not like 'D'"
                        + " left join hrm_ps_emp_spouses spouse on e.emp_seq = spouse.emp_seq"
                            + " and spouse.spouse_status = 1"
                            + " and spouse.record_status not like 'D'"
                        + " left join mas_name_prefixes fnp on father.name_prefix_seq = fnp.name_prefix_seq"
                        + " left join mas_name_prefixes mnp on mother.name_prefix_seq = mnp.name_prefix_seq"
                        + " left join mas_name_prefixes snp on spouse.name_prefix_seq = snp.name_prefix_seq"

                   + " WHERE e.RECORD_STATUS = @ActiveRecord"
                   + (Sequence.HasValue ? " AND e.EMP_SEQ = @Sequence" : "")
                   + (bActive ? " AND es.ACTIVE = 1 " : " AND es.ACTIVE = 0")
                   + (JobSiteSequence.HasValue ? " AND j.JOB_SITE_FULL_PATH LIKE CONCAT('%/', @JobSiteSequence , '/%')" : "")
                   + (WorkingJobSiteSequence.HasValue ? " AND wj.JOB_SITE_FULL_PATH LIKE CONCAT('%/', @WorkingJobSiteSequence , '/%')" : "")
                   
                   //+ " AND CASE WHEN @SearchText = '' THEN 0 = 0"
                   //     + " ELSE ("

                   + " AND e.FIRST_NAME LIKE CONCAT('%', @SearchText , '%')"
                                    + " OR e.LAST_NAME LIKE CONCAT('%', @SearchText , '%')"
                                    + " OR c.CLASS_NAME LIKE CONCAT('%', @SearchText , '%')"
                                    + " OR p.POS_NAME LIKE CONCAT('%', @SearchText , '%')"
                                    + " OR pj.JOB_SITE_NAME LIKE CONCAT('%', @SearchText , '%')"
                                //+ ") END"
                   //+ " ORDER BY p.POS_REF + 1"
                   + ")emp"
                   + " WHERE 0 =0 "
                   + (EmpTypeSequence.Length > 0 ? " AND EMP_TYPE_SEQ = @EmpTypeSequence" : "")
                   + (Sequence.HasValue ? " AND emp.EMP_SEQ = @Sequence" : "")
                   + (StartRetire > 0 ? EndRetire > 0 ? " AND YEAR(emp.RETIRE_DATE)+ 543 >= @StartRetire AND YEAR(emp.RETIRE_DATE)+ 543 <= @EndRetire"
                   : " AND YEAR(emp.RETIRE_DATE)+ 543 = @StartRetire" : "");

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
            }
            catch (Exception error)
            {
                throw error;
            }
            finally
            {
                dbManager.Dispose();
            }
            return ds;
        }

        public DataSet LoadEmployees(Nullable<int> Sequence, string EmpTypeSequence, Nullable<int> JobSiteSequence
            , Nullable<int> WorkingJobSiteSequence, bool bActive, int StartRetire, int EndRetire, string SearchText
            , string EmpStatusSequnce)
        {
            string strSQL;
            DataSet ds = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(10);

                dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@EmpTypeSequence", EmpTypeSequence);
                dbManager.AddParameters(2, "@Active", bActive);
                dbManager.AddParameters(3, "@JobSiteSequence", JobSiteSequence);
                dbManager.AddParameters(4, "@WorkingJobSiteSequence", WorkingJobSiteSequence);
                dbManager.AddParameters(5, "@Sequence", Sequence);
                dbManager.AddParameters(6, "@SearchText", SearchText);
                dbManager.AddParameters(7, "@StartRetire", StartRetire);
                dbManager.AddParameters(8, "@EndRetire", EndRetire);
                dbManager.AddParameters(9, "@EmpStatusSequnce", EmpStatusSequnce);

                strSQL = "SELECT emp.* FROM("
                        + "SELECT e.EMP_SEQ, e.NAME_PREFIX_SEQ, np.PREFIX_NAME, e.FIRST_NAME, e.LAST_NAME"
                        + ", e.FIRST_NAME_ENG, e.LAST_NAME_ENG, np.PREFIX_NAME_ENG"
                        + ", CONCAT(np.PREFIX_NAME, e.FIRST_NAME, ' ', e.LAST_NAME) AS FULL_NAME"
                        + ", CONCAT(isnull(np.PREFIX_NAME_ENG, ''), e.FIRST_NAME_ENG, ' ', e.LAST_NAME_ENG) AS FULL_NAME_ENG" // ifnull to isnull
                        + ", e.GENDER_SEQ, e.RELIGION_SEQ, e.RACE_NAT_SEQ, e.NAT_SEQ, e.BIRTH_DATE, e.EMP_TYPE_SEQ"
                        + ", e.CLASS_SEQ, e.SALARY_LEVEL_SEQ, e.SALARY, e.POS_SEQ, e.WORKING_JOB_SITE_SEQ"
                        + ", e.DATE_ADMITTED, e.DATE_WORK_START, e.DATE_QUIT, e.EMP_STATUS_SEQ" //, e.EMP_SEC_SEQ
                        + ", e.CITIZEN, e.TAX_PAYER , e.SOCIAL, e.OFFICIAL, e.BLOOD_GROUP_SEQ, e.PROBATION_DATE_END, e.DEATH_DATE"
                        + ", e.DATE_LATEST_POS, e.DATE_LATEST_CLASS, e.DATE_LATEST_WORKING_JOB_SITE"    //, e.FUND_MEMBER
                        + ", e.MAR_STAT_SEQ"
                        + ", p.POS_REF, p.POS_NAME, p.JOB_SITE_SEQ, pj.JOB_SITE_NAME"
                        + ", c.CLASS_NAME, wj.JOB_SITE_NAME AS WORKING_JOB_SITE_NAME, et.EMP_TYPE_NAME"
                        + ", gen.GENDER_NAME, re.RELIGION_NAME, rnat.NAT_NAME AS RACE_NAT_NAME, nat.NAT_NAME"
                        + ", es.EMP_STATUS_NAME, f.POS_TYPE_SEQ, b.BLOOD_GROUP_NAME, ms.MAR_STAT_NAME"
                        + ", CASE WHEN MONTH(e.BIRTH_DATE) >= 10 AND DAY(e.BIRTH_DATE) > 1"
                            + " THEN CAST(concat( YEAR(e.BIRTH_DATE) + 61, '-', '10-01') AS DATE) "
                            + " ELSE CAST(concat( YEAR(e.BIRTH_DATE) + 60, '-', '10-01') AS DATE) "
                            + " END AS RETIRE_DATE"
                        + ", f.FIELD_POS_NAME, ex.EXEC_POS_NAME"
                        + ", CONCAT(p.POS_REF, ' - ', p.POS_NAME) AS POS_TEXT"
                        + ", dbo.F_Get_Age(e.DATE_ADMITTED)  AS ADMITTED_COUNT"     //", F_Get_Age(e.DATE_ADMITTED)  AS ADMITTED_COUNT"
                        + ", dbo.F_Get_Age(e.DATE_LATEST_POS) AS POSITION_COUNT"    //", F_Get_Age(e.DATE_LATEST_POS) AS POSITION_COUNT"
                        //+ ", e.CODE_SCAN"
                        + ", e.image_unique"
                   + " FROM HRM_PS_EMPLOYEES e"
                        + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                        + " LEFT JOIN HRM_PS_EMPLOYEE_TYPES et ON e.EMP_TYPE_SEQ = et.EMP_TYPE_SEQ"
                        + " LEFT JOIN HRM_MP_CLASSES c ON e.CLASS_SEQ = c.CLASS_SEQ"
                        + " LEFT JOIN HRM_MP_POSITIONS p ON e.POS_SEQ = p.POS_SEQ"
                        + " LEFT JOIN HRM_MP_FIELD_POSITIONS f ON p.FIELD_POS_SEQ = f.FIELD_POS_SEQ"
                        + " LEFT JOIN HRM_MP_EXEC_POSITIONS ex ON p.EXEC_POS_SEQ = ex.EXEC_POS_SEQ"
                        + " LEFT JOIN HRM_MP_JOB_SITES pj ON p.JOB_SITE_SEQ = pj.JOB_SITE_SEQ"
                        + " LEFT JOIN HRM_MP_JOB_SITES wj ON e.WORKING_JOB_SITE_SEQ = wj.JOB_SITE_SEQ"
                        + " LEFT JOIN HRM_PS_EMPLOYEE_STATUSES es ON e.EMP_STATUS_SEQ = es.EMP_STATUS_SEQ"
                        + " LEFT JOIN MAS_GENDERS gen ON e.GENDER_SEQ = gen.GENDER_SEQ"
                        + " LEFT JOIN MAS_RELIGIONS re ON e.RELIGION_SEQ = re.RELIGION_SEQ"
                        + " LEFT JOIN MAS_NATIONALITIES rnat ON e.RACE_NAT_SEQ = rnat.NAT_SEQ"
                        + " LEFT JOIN MAS_NATIONALITIES nat ON e.NAT_SEQ = nat.NAT_SEQ"
                        + " LEFT JOIN MAS_BLOOD_GROUPS b ON e.BLOOD_GROUP_SEQ = b.BLOOD_GROUP_SEQ"
                        + " LEFT JOIN MAS_MARITAL_STATUSES ms ON e.MAR_STAT_SEQ = ms.MAR_STAT_SEQ"
                   + " WHERE e.RECORD_STATUS = @ActiveRecord"
                   + (Sequence.HasValue ? " AND e.EMP_SEQ = @Sequence" : "")
                   + (EmpTypeSequence.Length > 0 ? " AND e.EMP_TYPE_SEQ = @EmpTypeSequence" : "")
                   /*
                    * -1 แสดงทั้งหมด
                    * -2 แสดง Active
                    * -3 แสดง Inactive
                    */
                   + (EmpStatusSequnce != "-1" && EmpStatusSequnce != "-2" && EmpStatusSequnce != "-3" &&
                   EmpStatusSequnce.Length > 0 ? " AND e.EMP_STATUS_SEQ = @EmpStatusSequnce" : "")

                   + (
                   new ConvertNullable().ConvertNullInt(EmpStatusSequnce) >= 1 ? "" :
                       (EmpStatusSequnce != "-1" && bActive ? " AND es.ACTIVE = 1 " : "")
                       + (EmpStatusSequnce != "-1" && !bActive ? " AND es.ACTIVE = 0 " : "")
                   )

                   + (JobSiteSequence.HasValue ? " AND j.JOB_SITE_FULL_PATH LIKE CONCAT('%/', @JobSiteSequence , '/%')" : "")
                   + (WorkingJobSiteSequence.HasValue ? " AND wj.JOB_SITE_FULL_PATH LIKE CONCAT('%/', @WorkingJobSiteSequence , '/%')" : "")

                   //+ " AND CASE WHEN @SearchText = '' THEN 0 = 0"
                   //     + " ELSE (e.FIRST_NAME LIKE CONCAT('%', @SearchText , '%')"
                   //                 + " OR e.LAST_NAME LIKE CONCAT('%', @SearchText , '%')"
                   //                 + " OR c.CLASS_NAME LIKE CONCAT('%', @SearchText , '%')"
                   //                 + " OR p.POS_NAME LIKE CONCAT('%', @SearchText , '%')"
                   //                 + " OR pj.JOB_SITE_NAME LIKE CONCAT('%', @SearchText , '%')"
                   //             + ") END"
                   //+ " ORDER BY p.POS_REF + 1"
                         + " AND ( e.FIRST_NAME LIKE CONCAT('%', @SearchText , '%')"
                                     + " OR e.LAST_NAME LIKE CONCAT('%', @SearchText , '%')"
                                     + " OR c.CLASS_NAME LIKE CONCAT('%', @SearchText , '%')"
                                     + " OR p.POS_NAME LIKE CONCAT('%', @SearchText , '%')"
                                     + " OR pj.JOB_SITE_NAME LIKE CONCAT('%', @SearchText , '%') )"                    
                   + ")emp"
                   + " WHERE 0 =0 "
                   + (StartRetire > 0 ? EndRetire > 0 ? " AND YEAR(emp.RETIRE_DATE)+ 543 >= @StartRetire AND YEAR(emp.RETIRE_DATE)+ 543 <= @EndRetire"
                        : " AND YEAR(emp.RETIRE_DATE)+ 543 = @StartRetire" : "")
                   + " ORDER BY emp.POS_REF + 1";

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
            return ds;
        }
        public DataSet LoadToExcel(string EmployeeType, string JobSite, string WorkingJobSite
            , string PositionType, string Class, string FieldPositon, string EduDegree
            , string EduQualification, string EduMajor, string EduInstitutes
            , Nullable<double> WorkingDayCount, Nullable<DateTime> WorkingDayCountDate
            , Nullable<double> AgeCount, Nullable<DateTime> AgeCountDate
            , Nullable<int> KeepClass, Nullable<int> KeepClassCount
            , bool bActive)
        {
            string strSQL;
            bool bNonEduHis = (string.IsNullOrEmpty(EduDegree)
                            && string.IsNullOrEmpty(EduQualification)
                            && string.IsNullOrEmpty(EduMajor)
                            && string.IsNullOrEmpty(EduInstitutes));
            DataSet ds = null; 

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(18);

                dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@EmpTypeSequence", EmployeeType);
                dbManager.AddParameters(2, "@Active", bActive);
                dbManager.AddParameters(3, "@JobSite", "%/" + JobSite + "/%");
                dbManager.AddParameters(4, "@WorkingJobSite", "%/" + WorkingJobSite + "/%");
                dbManager.AddParameters(5, "@PositionType", PositionType);
                dbManager.AddParameters(6, "@Class", Class);
                dbManager.AddParameters(7, "@FieldPositon", FieldPositon);

                dbManager.AddParameters(8, "@EduDegree", EduDegree);
                dbManager.AddParameters(9, "@EduQualification", EduQualification);
                dbManager.AddParameters(10, "@EduMajor", EduMajor);
                dbManager.AddParameters(11, "@EduInstitutes", EduInstitutes);

                dbManager.AddParameters(12, "@AgeCount", AgeCount);
                dbManager.AddParameters(13, "@AgeCountDate", AgeCountDate.HasValue ? AgeCountDate : DateTime.Now);

                dbManager.AddParameters(14, "@WorkingDayCount", WorkingDayCount);
                dbManager.AddParameters(15, "@WorkingDayCountDate", WorkingDayCountDate);
                dbManager.AddParameters(16, "@KeepClass", KeepClass);
                dbManager.AddParameters(17, "@KeepClassCount", KeepClassCount);

				/*
                strSQL = @"
SELECT CONCAT(np.PREFIX_NAME, e.FIRST_NAME, ' ', e.LAST_NAME) 'ชื่อ-นามสกุล'
    , CONCAT(np.PREFIX_NAME_ENG, e.FIRST_NAME_ENG, ' ', e.LAST_NAME_ENG) 'ชื่อ-นามสกุลภาษาอังกฤษ'
    , e.BIRTH_DATE 'วันเกิด'
    , p.POS_REF 'เลขตำแหน่ง'
    , p.POS_NAME 'ตำแหน่ง'
    , c.CLASS_NAME 'ระดับ'
    , pj.JOB_SITE_NAME 'สังกัดตามโครงสร้างกฎกระทรวง'
    , wj.JOB_SITE_NAME 'สังกัดตามโครงสร้างการบริหารจัดการ'
    , CASE WHEN MONTH(e.BIRTH_DATE) >= 10 AND DAY(e.BIRTH_DATE) > 1
        THEN CAST(concat( YEAR(e.BIRTH_DATE) + 61, '-', '10-01') AS CHAR) 
        ELSE CAST(concat( YEAR(e.BIRTH_DATE) + 60, '-', '10-01') AS CHAR) 
        END as 'วันเกษียรอายุราชการ'
    , e.CITIZEN 'รหัสประจำตัวประชาชน' 
    , e.SALARY 'เงินเดือน' 
    , e.DATE_ADMITTED 'วันที่บรรจุ' 
    , e.DATE_WORK_START 'วันที่เข้าสู่สังกัดกรม'
    , e.DATE_LATEST_POS 'วันที่ดำรงตำแหน่งสุดท้าย' 
    , e.DATE_LATEST_CLASS 'วันที่ดำรงระดับสุดท้าย' 
    , e.DATE_LATEST_WORKING_JOB_SITE 'วันที่ทำงานในสังกัดวันสุดท้าย'

FROM HRM_PS_EMPLOYEES e
    LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ
    LEFT JOIN HRM_PS_EMPLOYEE_TYPES et ON e.EMP_TYPE_SEQ = et.EMP_TYPE_SEQ
    LEFT JOIN HRM_MP_CLASSES c ON e.CLASS_SEQ = c.CLASS_SEQ
    LEFT JOIN HRM_MP_POSITIONS p ON e.POS_SEQ = p.POS_SEQ
    LEFT JOIN HRM_MP_FIELD_POSITIONS f ON p.FIELD_POS_SEQ = f.FIELD_POS_SEQ
    LEFT JOIN HRM_MP_JOB_SITES pj ON p.JOB_SITE_SEQ = pj.JOB_SITE_SEQ
    LEFT JOIN HRM_MP_JOB_SITES wj ON e.WORKING_JOB_SITE_SEQ = wj.JOB_SITE_SEQ
    LEFT JOIN HRM_PS_EMPLOYEE_STATUSES es ON e.EMP_STATUS_SEQ = es.EMP_STATUS_SEQ
    LEFT JOIN MAS_GENDERS gen ON e.GENDER_SEQ = gen.GENDER_SEQ
    LEFT JOIN MAS_RELIGIONS re ON e.RELIGION_SEQ = re.RELIGION_SEQ
    LEFT JOIN MAS_NATIONALITIES rnat ON e.RACE_NAT_SEQ = rnat.NAT_SEQ
    LEFT JOIN MAS_NATIONALITIES nat ON e.NAT_SEQ = nat.NAT_SEQ  
               
WHERE e.RECORD_STATUS = @ActiveRecord "
+ (PositionType.Length > 0 ? " AND f.POS_TYPE_SEQ IN(" + PositionType + ")" : "")
+ (Class.Length > 0 ? " AND e.CLASS_SEQ IN(" + Class + ")" : "")
+ (FieldPositon.Length > 0 ? " AND p.FIELD_POS_SEQ IN(" + FieldPositon + ")" : "")                  
+ (EmployeeType.Length > 0 ? " AND e.EMP_TYPE_SEQ = @EmpTypeSequence" : "")
+ (JobSite.Length > 0 ? " AND pj.JOB_SITE_FULL_PATH LIKE @JobSite" : "")
+ (WorkingJobSite.Length > 0 ? " AND wj.JOB_SITE_FULL_PATH LIKE @WorkingJobSite" : "")
+ (bActive ? " AND es.ACTIVE = 1 " : " AND es.ACTIVE = 0")
+ (AgeCount > 0 ? " AND F_Get_Age_Count(e.BIRTH_DATE, @AgeCountDate) = @AgeCount" : "")
+ (WorkingDayCount > 0 ? " AND F_Get_Age_Count(e.DATE_ADMITTED, @WorkingDayCountDate) = @WorkingDayCount" : "")
+ (KeepClass.HasValue && KeepClass > 0 ? " AND (e.CLASS_SEQ = @KeepClass"
+ " AND F_Get_Age_Count(e.DATE_LATEST_CLASS, getdate()) = @KeepClassCount)" : "")

// Join ประวัติการศึกษา  
+ (bNonEduHis ? "" : " AND e.EMP_SEQ IN ("
    + " select h.emp_seq"
    + " from hrm_ps_emp_educations h"
        + " left join mas_edu_qualifications q on h.edu_qual_seq = q.edu_qual_seq"
        + " left join mas_edu_degrees dg on q.edu_degree_seq = dg.edu_degree_seq"
        + " left join mas_edu_majors m on h.edu_major_seq = m.edu_major_seq"
        + " left join mas_edu_institutes i on h.edu_ins_seq = i.edu_ins_seq"
    + " where h.record_status = @ActiveRecord"
    + (EduDegree.Length > 0 ? " and q.edu_degree_seq in(" + EduDegree + ")" : "")
    + (EduQualification.Length > 0 ? " and h.edu_qual_seq in(" + EduQualification + ")" : "")
    + (EduMajor.Length > 0 ? " and h.edu_major_seq in(" + EduMajor + ")" : "")
    + (EduInstitutes.Length > 0 ? " and h.edu_ins_seq in(" + EduInstitutes + ")" : "")
+ ")")

+ " ORDER BY p.POS_REF + 1";
				*/

				// ก่อนแก้ไข
                strSQL = "SELECT CONCAT(np.PREFIX_NAME, e.FIRST_NAME, ' ', e.LAST_NAME) AS NAME"
                        + ", CONCAT(np.PREFIX_NAME_ENG, e.FIRST_NAME_ENG, ' ', e.LAST_NAME_ENG) AS FULL_NAME_ENG"
                        + ", e.BIRTH_DATE AS BIRTH_DATE, p.POS_REF AS POSITION_NO, p.POS_NAME AS POSITION_NAME"
                        + ", c.CLASS_NAME AS CLASS_LEVEL, pj.JOB_SITE_NAME AS JOB_SITE_NAME"
                        + ", wj.JOB_SITE_NAME AS WORKING_JOB_SITE_NAME"
						+ ", CASE WHEN MONTH(e.BIRTH_DATE) >= 10 AND DAY(e.BIRTH_DATE) > 1"
                            //+ " THEN Date(CAST(concat( YEAR(e.BIRTH_DATE) + 61, '-', '10-01') AS CHAR)) "
                            //+ " ELSE Date(CAST(concat( YEAR(e.BIRTH_DATE) + 60, '-', '10-01') AS CHAR)) "
                
                        + " THEN CAST(concat( YEAR(e.BIRTH_DATE) + 61, '-', '10-01') AS CHAR) "
                        + " ELSE CAST(concat( YEAR(e.BIRTH_DATE) + 60, '-', '10-01') AS CHAR) "
                    
                            + " END AS RETIRE_DATE"
                        + ", e.CITIZEN, e.SALARY, e.DATE_ADMITTED, e.DATE_WORK_START"
                        + ", e.DATE_LATEST_POS, e.DATE_LATEST_CLASS, e.DATE_LATEST_WORKING_JOB_SITE"
                        //+ ", e.CLASS_SEQ, e.SALARY_LEVEL_SEQ, e.POS_SEQ, e.WORKING_JOB_SITE_SEQ"
                        //+ ", e.DATE_QUIT, e.EMP_STATUS_SEQ, e.EMP_SEC_SEQ, b.BLOOD_GROUP_NAME"
                        //+ ", e.TAX_PAYER , e.SOCIAL, e.OFFICIAL, e.BLOOD_GROUP_SEQ, e.PROBATION_DATE_END, e.DEATH_DATE"
                        //+ ", et.EMP_TYPE_NAME, gen.GENDER_NAME, re.RELIGION_NAME, rnat.NAT_NAME AS RACE_NAT_NAME, nat.NAT_NAME"
                        //+ ", es.EMP_STATUS_NAME, f.POS_TYPE_SEQ, ms.MAR_STAT_NAME, e.FUND_MEMBER"
                   + " FROM HRM_PS_EMPLOYEES e"
                        + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                        + " LEFT JOIN HRM_PS_EMPLOYEE_TYPES et ON e.EMP_TYPE_SEQ = et.EMP_TYPE_SEQ"
                        + " LEFT JOIN HRM_MP_CLASSES c ON e.CLASS_SEQ = c.CLASS_SEQ"
                        + " LEFT JOIN HRM_MP_POSITIONS p ON e.POS_SEQ = p.POS_SEQ"
                        + " LEFT JOIN HRM_MP_FIELD_POSITIONS f ON p.FIELD_POS_SEQ = f.FIELD_POS_SEQ"
                        + " LEFT JOIN HRM_MP_JOB_SITES pj ON p.JOB_SITE_SEQ = pj.JOB_SITE_SEQ"
                        + " LEFT JOIN HRM_MP_JOB_SITES wj ON e.WORKING_JOB_SITE_SEQ = wj.JOB_SITE_SEQ"
                        + " LEFT JOIN HRM_PS_EMPLOYEE_STATUSES es ON e.EMP_STATUS_SEQ = es.EMP_STATUS_SEQ"
                        + " LEFT JOIN MAS_GENDERS gen ON e.GENDER_SEQ = gen.GENDER_SEQ"
                        + " LEFT JOIN MAS_RELIGIONS re ON e.RELIGION_SEQ = re.RELIGION_SEQ"
                        + " LEFT JOIN MAS_NATIONALITIES rnat ON e.RACE_NAT_SEQ = rnat.NAT_SEQ"
                        + " LEFT JOIN MAS_NATIONALITIES nat ON e.NAT_SEQ = nat.NAT_SEQ"
                        //+ " LEFT JOIN MAS_BLOOD_GROUPS b ON e.BLOOD_GROUP_SEQ = b.BLOOD_GROUP_SEQ"                       
                       + " WHERE e.RECORD_STATUS = @ActiveRecord"
                   + (PositionType.Length > 0 ? " AND f.POS_TYPE_SEQ IN(" + PositionType + ")" : "")
                   + (Class.Length > 0 ? " AND e.CLASS_SEQ IN(" + Class + ")" : "")
                   + (FieldPositon.Length > 0 ? " AND p.FIELD_POS_SEQ IN(" + FieldPositon + ")" : "")                  
                   + (EmployeeType.Length > 0 ? " AND e.EMP_TYPE_SEQ = @EmpTypeSequence" : "")
                   + (JobSite.Length > 0 ? " AND pj.JOB_SITE_FULL_PATH LIKE @JobSite" : "")
                   + (WorkingJobSite.Length > 0 ? " AND wj.JOB_SITE_FULL_PATH LIKE @WorkingJobSite" : "")
                   + (bActive ? " AND es.ACTIVE = 1 " : " AND es.ACTIVE = 0")
                   + (AgeCount > 0 ? " AND F_Get_Age_Count(e.BIRTH_DATE, @AgeCountDate) = @AgeCount" : "")
                   + (WorkingDayCount > 0 ? " AND F_Get_Age_Count(e.DATE_ADMITTED, @WorkingDayCountDate) = @WorkingDayCount" : "")
                   + (KeepClass.HasValue && KeepClass > 0 ? " AND (e.CLASS_SEQ = @KeepClass"
                                        + " AND F_Get_Age_Count(e.DATE_LATEST_CLASS, GETDATE()) = @KeepClassCount)" : "")
                    // Join ประวัติการศึกษา ***********************************
                   + (bNonEduHis ? "" : " AND e.EMP_SEQ IN ("
                        + " select h.emp_seq"
                            //+ " h.edu_seq, h.emp_seq, h.edu_degree_seq, h.edu_qual_seq, h.edu_major_seq"
                            //+ ", h.edu_ins_seq, h.edu_start_year, h.edu_end_year, h.start_date, h.end_date"
                            //+ ", h.gpa, h.remarks, h.record_status, h.created_by, h.updated_by"
                            //+ ", q.edu_qual_name, m.edu_major_name, i.edu_ins_name, dg.edu_degree_name"
                            //+ ", i.country_seq "
                        + " from hrm_ps_emp_educations h"
                            + " left join mas_edu_qualifications q on h.edu_qual_seq = q.edu_qual_seq"
                            + " left join mas_edu_degrees dg on q.edu_degree_seq = dg.edu_degree_seq"
                            + " left join mas_edu_majors m on h.edu_major_seq = m.edu_major_seq"
                            + " left join mas_edu_institutes i on h.edu_ins_seq = i.edu_ins_seq"
                        + " where h.record_status = @ActiveRecord"
                        + (EduDegree.Length > 0 ? " and q.edu_degree_seq in(" + EduDegree + ")" : "")
                        + (EduQualification.Length > 0 ? " and h.edu_qual_seq in(" + EduQualification + ")" : "")
                        + (EduMajor.Length > 0 ? " and h.edu_major_seq in(" + EduMajor + ")" : "")
                        + (EduInstitutes.Length > 0 ? " and h.edu_ins_seq in(" + EduInstitutes + ")" : "")
                    + ")")
                    //******************************************************
                   + " ORDER BY p.POS_REF + 1";

				Thread.CurrentThread.CurrentCulture = new CultureInfo("en-US");
				Thread.CurrentThread.CurrentUICulture = new CultureInfo("en-US");

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
            }
            catch (Exception err)
            {
	            throw new Exception(err.Message);
            }
            finally
            {
                dbManager.Dispose();
            }
            return ds;
        }
        public DataSet LoadEmployeeForWorkingOversea(Nullable<int> Sequence, string EmpTypeSequence
            , Nullable<int> WorkingJobSiteSequence, int DurationOversea, Nullable<DateTime> StartDateNew
            , string SearchText)
        {
            string strSQL;
            DataSet ds = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(9);

                dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@EmpTypeSequence", EmpTypeSequence);
                dbManager.AddParameters(2, "@WorkingJobSiteSequence", WorkingJobSiteSequence);
                dbManager.AddParameters(3, "@Sequence", Sequence);
                dbManager.AddParameters(4, "@SearchText", SearchText);
                dbManager.AddParameters(5, "@DurationOversea", DurationOversea);
                dbManager.AddParameters(6, "@StartDateNew", StartDateNew);
                dbManager.AddParameters(7, "@ApproveRecord", SystemInfo._ApproveRecordStatus);
                dbManager.AddParameters(8, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT emp.*, coalesce(sub.REMAIN_AMOUNT, 0) AS REMAIN_AMOUNT"
                    + " FROM("
                        + "SELECT e.EMP_SEQ, e.EMP_TYPE_SEQ"
                        + ", CONCAT(np.PREFIX_NAME, e.FIRST_NAME, ' ', e.LAST_NAME) AS FULL_NAME"
                        + ", CONCAT(isnull(np.PREFIX_NAME_ENG, ''), e.FIRST_NAME_ENG, ' ', e.LAST_NAME_ENG) AS FULL_NAME_ENG"   // ifnull to isnull
                        + ", e.CLASS_SEQ, e.SALARY, e.POS_SEQ, e.WORKING_JOB_SITE_SEQ, e.EMP_STATUS_SEQ"
                    //+ ", e.DATE_ADMITTED, e.DATE_WORK_START, e.DATE_QUIT, e.EMP_SEC_SEQ, e.SALARY_LEVEL_SEQ"
                        + ", p.POS_REF, p.POS_NAME, p.JOB_SITE_SEQ, pj.JOB_SITE_NAME"
                        + ", c.CLASS_NAME, wj.JOB_SITE_NAME AS WORKING_JOB_SITE_NAME, et.EMP_TYPE_NAME"
                   + " FROM HRM_PS_EMPLOYEES e"
                        + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
                        + " LEFT JOIN HRM_PS_EMPLOYEE_TYPES et ON e.EMP_TYPE_SEQ = et.EMP_TYPE_SEQ"
                        + " LEFT JOIN HRM_MP_CLASSES c ON e.CLASS_SEQ = c.CLASS_SEQ"
                        + " LEFT JOIN HRM_MP_POSITIONS p ON e.POS_SEQ = p.POS_SEQ"
                        + " LEFT JOIN HRM_MP_FIELD_POSITIONS f ON p.FIELD_POS_SEQ = f.FIELD_POS_SEQ"
                        + " LEFT JOIN HRM_MP_EXEC_POSITIONS ex ON p.EXEC_POS_SEQ = ex.EXEC_POS_SEQ"
                        + " LEFT JOIN HRM_MP_JOB_SITES pj ON p.JOB_SITE_SEQ = pj.JOB_SITE_SEQ"
                        + " LEFT JOIN HRM_MP_JOB_SITES wj ON e.WORKING_JOB_SITE_SEQ = wj.JOB_SITE_SEQ"
                        + " LEFT JOIN HRM_PS_EMPLOYEE_STATUSES es ON e.EMP_STATUS_SEQ = es.EMP_STATUS_SEQ"                        
                   + " WHERE e.RECORD_STATUS = @ActiveRecord"
                   + " AND es.ACTIVE not like 0"
                   + (Sequence.HasValue ? " AND e.EMP_SEQ = @Sequence" : "")
                   + (EmpTypeSequence.Length > 0 ? " AND e.EMP_TYPE_SEQ = @EmpTypeSequence" : "")
                    //+ (JobSiteSequence.HasValue ? " AND j.JOB_SITE_FULL_PATH LIKE CONCAT('%/', @JobSiteSequence , '/%')" : "")
                   + (WorkingJobSiteSequence.HasValue ? " AND wj.JOB_SITE_FULL_PATH LIKE CONCAT('%/', @WorkingJobSiteSequence , '/%')" : "")

                   + " AND CASE WHEN @SearchText = '' THEN 0 = 0"
                        + " ELSE (e.FIRST_NAME LIKE CONCAT('%', @SearchText , '%')"
                                    + " OR e.LAST_NAME LIKE CONCAT('%', @SearchText , '%')"
                                    + " OR c.CLASS_NAME LIKE CONCAT('%', @SearchText , '%')"
                                    + " OR p.POS_NAME LIKE CONCAT('%', @SearchText , '%')"
                                    + " OR wj.JOB_SITE_NAME LIKE CONCAT('%', @SearchText , '%')"
                                + ") END"
                   + " ORDER BY p.POS_REF + 1"
                   + ")emp"
                   + " LEFT JOIN("
                        + "select rq.debtor_emp_seq"//rq.doc_ref_no, rq.money_req_name, rq.borrow_return_date"
                            + ", coalesce(rqd.req_amount, 0) - coalesce(ret.ret_amount, 0) - coalesce(rec.rec_amount, 0) as remain_amount"
                            + " from fim_money_requests rq"
                            + " left join (select money_req_seq"
                                + ", sum(coalesce(bath_amount, 0)) as req_amount"
                                + " from fim_money_request_dtls"
                                + " where record_status not like @DeleteRecord"
                                + " group by money_req_seq"
                                + ") rqd on rq.money_req_seq = rqd.money_req_seq"
                            + " left join (select re.money_req_seq"
                                + ", sum(coalesce(red.bath_amount, 0)) as ret_amount"
                                + " from fim_money_returns re"
                                + " left join fim_money_return_dtls red on re.return_seq = red.return_seq"
                                    + " and red.record_status not like @DeleteRecord"
                                + " where re.record_status not like @DeleteRecord"
                                + " group by re.money_req_seq"
                                + ") ret on rq.money_req_seq = ret.money_req_seq"
                            + " left join (select money_req_seq"
                                + ", sum(coalesce(bath_amount, 0)) as rec_amount"
                                + " from fim_receives"
                                + " where record_status not like @DeleteRecord"
                                + " group by money_req_seq"
                                + ") rec on rq.money_req_seq = rec.money_req_seq"
                            + " where rq.record_status = @ApproveRecord"
                            //+ " and rq.debtor_emp_seq = emp.EMP_SEQ"// -- emp_seq ของผู้ยืม
                            + " and rq.event_seq = 16"// -- ตายตัว"
                        + ")sub ON emp.EMP_SEQ = sub.DEBTOR_EMP_SEQ"
                   + " WHERE 0 = 0 "
                   + (DurationOversea > 0 ? " AND emp.EMP_SEQ IN(select a.emp_seq "
                            + " from hrm_ps_emp_activities a"
                            + " where F_Get_Age_Count(a.walk_end_date, @StartDateNew) * 12 >= @DurationOversea "
                            + ")" : "");

                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
            return ds;
        }
        public DataSet LoadEmployeeForApproveCommands()
        {
            DataSet ds = null;

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(1, "@DSClass", "51, 52");
                dbManager.AddParameters(2, "@EmployeeType", 1);
                
                string strSQL;
                strSQL = " select *,e.emp_seq as sequence, e.pos_seq as t_pos_seq, e.class_seq as t_class_seq "
                    + ", concat(np.prefix_name, e.first_name, '  ', e.last_name) as emp_name"
                    + ", ex.exec_pos_name"
                    + " from hrm_ps_employees as e"
                    + " left join hrm_mp_positions mp on e.pos_seq = mp.pos_seq"
                    + " left join hrm_mp_job_sites js on e.working_job_site_seq = js.job_site_seq"
                    + " left join hrm_mp_classes mc on e.class_seq = mc.class_seq"
                    + " left join mas_name_prefixes np on e.name_prefix_seq = np.name_prefix_seq"
                    + " left join hrm_mp_exec_positions ex on mp.exec_pos_seq = ex.exec_pos_seq"
                    + " where e.record_status = @ActiveRecord"
                         + " and e.class_seq in (51, 52) "
                         + " and e.emp_type_seq = @EmployeeType"
                         + " and e.emp_status_seq = 101"
                         + " order by mp.pos_ref + 0";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

                return ds;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                dbManager.Dispose();
            }
            
        }

        public DataSet Detail()
        {
            DataSet ds = null;

            try
            {
                dbManager.Open();

                string strSQL;
                strSQL = @"SELECT 
	e.emp_seq
	, e.citizen AS 'emp citizen'
	, CONCAT(e.first_name, ' ', e.last_name) AS 'emp full name'
	, e.record_status AS 'emp record status'
	, CONCAT(s.emp_status_name, ' (', IF(s.active = '1', 'Active', 'ไม่ Active'), ')') AS 'emp status' 
	, IF(u.user_code is null, 'ไม่มี User', u.user_code) AS 'user name'
	, CONCAT(u.user_first_name, ' ', u.user_last_name) AS 'user full name'
	, u.user_seq
	, u.user_citizen AS 'user citizen'
	, u.record_status AS 'user record status'
FROM hrm_ps_employees e
	LEFT JOIN sys_sc_users u ON e.emp_seq = u.emp_seq 
	LEFT JOIN hrm_ps_employee_statuses s ON e.emp_status_seq = s.emp_status_seq 
ORDER BY s.active DESC, u.user_code DESC,e.emp_seq ASC
	 ";
                ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);

                return ds;
            }
            catch (Exception ex)
            {
                return null;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public string Insert(EmployeeInfo Info)
        {
            string strSQL = "";
            string strSeq;

            try{
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(39);

                // Edit: Solve check sub class is null is not enough, because they sent here as default value class (sequence = 0, int = 0, string = null)
                dbManager.AddParameters(0, "@name_prefix_seq", Info.Person.NamePrefix == null || Info.Person.NamePrefix.Sequence == 0 ? (int?)null : Info.Person.NamePrefix.Sequence);
                dbManager.AddParameters(1, "@first_name", Info.Person.FirstName);
                dbManager.AddParameters(2, "@last_name", Info.Person.LastName);
                dbManager.AddParameters(3, "@first_name_eng", Info.Person.FirstNameEng);
                dbManager.AddParameters(4, "@last_name_eng", Info.Person.LastNameEng);
                dbManager.AddParameters(5, "@gender_seq", Info.Person.Gender == null || Info.Person.Gender.Sequence == 0 ? (int?)null : Info.Person.Gender.Sequence);
                dbManager.AddParameters(6, "@religion_seq", Info.Person.Religion == null || Info.Person.Religion.Sequence == 0 ? (int?)null : Info.Person.Religion.Sequence);
                dbManager.AddParameters(7, "@race_nat_seq", Info.Person.RaceNationalities == null || Info.Person.RaceNationalities.Sequence == 0 ? (int?)null : Info.Person.RaceNationalities.Sequence);
                dbManager.AddParameters(8, "@nat_seq", Info.Person.Nationalities == null || Info.Person.Nationalities.Sequence == 0 ? (int?)null : Info.Person.Nationalities.Sequence);
                dbManager.AddParameters(9, "@birth_date", Info.Person.BirthDate);
                dbManager.AddParameters(10, "@emp_type_seq", Info.EmployeeType == null || Info.EmployeeType.Sequence == 0 ? (int?)null : Info.EmployeeType.Sequence);
                dbManager.AddParameters(11, "@class_seq", Info.Class == null || Info.Class.Sequence == 0 ? (int?)null : Info.Class.Sequence);
                dbManager.AddParameters(12, "@salary_level_seq", Info.SalaryLevel);
                dbManager.AddParameters(13, "@salary", Info.Salary);
                dbManager.AddParameters(14, "@pos_seq", Info.Position == null || Info.Position.Sequence == 0 ? (int?)null : Info.Position.Sequence);
                dbManager.AddParameters(15, "@working_job_site_seq", Info.WorkingJobSite == null || Info.WorkingJobSite.Sequence == 0 ? (int?)null : Info.WorkingJobSite.Sequence);
                dbManager.AddParameters(16, "@date_admitted", Info.AdmittedDate);
                dbManager.AddParameters(17, "@date_work_start", Info.WorkStartDate);
                dbManager.AddParameters(18, "@date_quit", Info.QuitDate);
                dbManager.AddParameters(19, "@emp_status_seq", Info.Status == null || Info.Status.Sequence == 0 ? (int?)null : Info.Status.Sequence);
                dbManager.AddParameters(20, "@emp_sec_seq", Info.Section);
                dbManager.AddParameters(21, "@citizen", Info.Person.Citizen);
                dbManager.AddParameters(22, "@tax_payer", Info.Person.TaxPayer);
                dbManager.AddParameters(23, "@social", Info.Person.Social);
                dbManager.AddParameters(24, "@blood_group", Info.Person.BloodGroup == null || Info.Person.BloodGroup.Sequence == 0 ? (int?)null : Info.Person.BloodGroup.Sequence);
                dbManager.AddParameters(25, "@probation_date_end", Info.ProbationEndDate);
                dbManager.AddParameters(26, "@death_date", Info.Person.DeathDate);
                dbManager.AddParameters(27, "@date_latest_pos", Info.LatestPositionDate);
                dbManager.AddParameters(28, "@date_latest_class", Info.LatestClassDate);
                dbManager.AddParameters(29, "@date_latest_working_job_site", Info.LatestWorkingJobSiteDate);
                dbManager.AddParameters(30, "@fund_member", Info.FundMember);
                dbManager.AddParameters(31, "@nick_name", Info.Person.NickName);
                dbManager.AddParameters(32, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(33, "@created_by", Info.CreatedBy);
				dbManager.AddParameters(34, "@created_when", DateTime.Today);
				dbManager.AddParameters(35, "@mar_stat_seq", Info.Person.MaritalStatus == null || Info.Person.MaritalStatus.Sequence == 0 ? (int?)null : Info.Person.MaritalStatus.Sequence);
                dbManager.AddParameters(36, "@official", Info.Person.Official);
                dbManager.AddParameters(37, "@code_scan", Info.CodeScan);
                dbManager.AddParameters(38, "@image_unique", Info.ImageUnique);

                strSQL = "INSERT INTO HRM_PS_EMPLOYEES"
                    + "(NAME_PREFIX_SEQ, FIRST_NAME, LAST_NAME, FIRST_NAME_ENG, LAST_NAME_ENG, NICK_NAME"
                        + ", GENDER_SEQ, RELIGION_SEQ, RACE_NAT_SEQ, NAT_SEQ, BIRTH_DATE, EMP_TYPE_SEQ"
                        + ", CLASS_SEQ, SALARY_LEVEL_SEQ, SALARY, POS_SEQ, WORKING_JOB_SITE_SEQ"
                        + ", DATE_ADMITTED, DATE_WORK_START, DATE_QUIT, EMP_STATUS_SEQ"//, EMP_SEC_SEQ"
                        + ", CITIZEN, TAX_PAYER, SOCIAL, BLOOD_GROUP_SEQ, PROBATION_DATE_END, DEATH_DATE"
                        + ", DATE_LATEST_POS, DATE_LATEST_CLASS, DATE_LATEST_WORKING_JOB_SITE"//, FUND_MEMBER"
                        + ", RECORD_STATUS, CREATED_BY, CREATED_WHEN"
                        + ", OFFICIAL "
                        + ", MAR_STAT_SEQ "
                        //+ ", CODE_SCAN "
                        + ", image_unique"
						+ ")"
                    + " VALUES (@name_prefix_seq, @first_name, @last_name, @first_name_eng, @last_name_eng, @nick_name"
                        + ", @gender_seq, @religion_seq, @race_nat_seq, @nat_seq, @birth_date, @emp_type_seq"
                        + ", @class_seq, @salary_level_seq, @salary, @pos_seq, @working_job_site_seq, @date_admitted"
                        + ", @date_work_start, @date_quit, @emp_status_seq, @citizen, @tax_payer, @social"
                        + ", @blood_group, @probation_date_end, @death_date, @date_latest_pos, @date_latest_class"
                        + ", @date_latest_working_job_site, @record_status, @created_by, @created_when"
						+ ", @official, @MAR_STAT_SEQ, @image_unique"//, @CODE_SCAN, @emp_sec_seq, @fund_member
                        + ");"
                    + " select scope_identity()";
                 
                //dbManager.ExecuteNonQuery(CommandType.Text, strSQL);

                //strSQL = "select last_insert_id() as last_seq from fim_money_requests";

                strSeq = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0][0].ToString();

                dbManager.CommitTransaction();

            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }

            if (Info.Position != null && Info.Position.PositionType != null && !String.IsNullOrEmpty(strSeq))
            {
                UpdateFieldPositionType(Convert.ToInt32(strSeq), Info.Position.PositionType.Sequence);
            }
            if (Info.Position != null)
            {
                UpdateFieldExecPositionSeq(Info.Position.Sequence, Info.Position.Executive == null ? null : Info.Position.Executive.Sequence);
            }
            
            return strSeq;
        }

        public void Update(EmployeeInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(40);

                dbManager.AddParameters(0, "@name_prefix_seq", Info.Person.NamePrefix.Sequence == 0 ? (int?)null : Info.Person.NamePrefix.Sequence);
                dbManager.AddParameters(1, "@first_name", Info.Person.FirstName);
                dbManager.AddParameters(2, "@last_name", Info.Person.LastName);
                dbManager.AddParameters(3, "@first_name_eng", Info.Person.FirstNameEng);
                dbManager.AddParameters(4, "@last_name_eng", Info.Person.LastNameEng);
                dbManager.AddParameters(5, "@gender_seq", Info.Person.Gender.Sequence == 0 ? (int?)null : Info.Person.Gender.Sequence);
                dbManager.AddParameters(6, "@religion_seq", Info.Person.Religion.Sequence == 0 ? (int?)null : Info.Person.Religion.Sequence);
                dbManager.AddParameters(7, "@race_nat_seq", Info.Person.RaceNationalities.Sequence == 0 ? (int?)null : Info.Person.RaceNationalities.Sequence);
                dbManager.AddParameters(8, "@nat_seq", Info.Person.Nationalities.Sequence == 0 ? (int?)null : Info.Person.Nationalities.Sequence);
                dbManager.AddParameters(9, "@birth_date", Info.Person.BirthDate);
                dbManager.AddParameters(10, "@emp_type_seq", Info.EmployeeType.Sequence == 0 ? (int?)null : Info.EmployeeType.Sequence);
                dbManager.AddParameters(11, "@class_seq", Info.Class.Sequence == 0 ? (int?)null : Info.Class.Sequence);
                dbManager.AddParameters(12, "@salary_level_seq", Info.SalaryLevel);
                dbManager.AddParameters(13, "@salary", Info.Salary);
                dbManager.AddParameters(14, "@pos_seq", Info.Position.Sequence == 0 ? (int?)null : Info.Position.Sequence);
                dbManager.AddParameters(15, "@working_job_site_seq", Info.WorkingJobSite.Sequence == 0 ? (int?)null : Info.WorkingJobSite.Sequence);
                dbManager.AddParameters(16, "@date_admitted", Info.AdmittedDate);
                dbManager.AddParameters(17, "@date_work_start", Info.WorkStartDate);
                dbManager.AddParameters(18, "@date_quit", Info.QuitDate);
                dbManager.AddParameters(19, "@emp_status_seq", Info.Status.Sequence == 0 ? (int?)null : Info.Status.Sequence);
                dbManager.AddParameters(20, "@emp_sec_seq", Info.Section);
                dbManager.AddParameters(21, "@citizen", Info.Person.Citizen);
                dbManager.AddParameters(22, "@tax_payer", Info.Person.TaxPayer);
                dbManager.AddParameters(23, "@social", Info.Person.Social);
                dbManager.AddParameters(24, "@blood_group", Info.Person.BloodGroup.Sequence == 0 ? (int?)null : Info.Person.BloodGroup.Sequence);
                dbManager.AddParameters(25, "@probation_date_end", Info.ProbationEndDate);
                dbManager.AddParameters(26, "@death_date", Info.Person.DeathDate);
                dbManager.AddParameters(27, "@date_latest_pos", Info.LatestPositionDate);
                dbManager.AddParameters(28, "@date_latest_class", Info.LatestClassDate);
                dbManager.AddParameters(29, "@date_latest_working_job_site", Info.LatestWorkingJobSiteDate);
                dbManager.AddParameters(30, "@fund_member", Info.FundMember ? 1 : 0);
                dbManager.AddParameters(31, "@emp_seq", Info.Sequence);
                dbManager.AddParameters(32, "@nick_name", Info.Person.NickName);
                dbManager.AddParameters(33, "@mar_stat_seq", Info.Person.MaritalStatus.Sequence == 0 ? (int?)null : Info.Person.MaritalStatus.Sequence);
                dbManager.AddParameters(34, "@official", Info.Person.Official);
                dbManager.AddParameters(35, "@code_scan", Info.CodeScan);
                dbManager.AddParameters(36, "@updated_by", Info.UpdatedBy);
				dbManager.AddParameters(37, "@updated_when", DateTime.Today);
				dbManager.AddParameters(38, "@image_unique", Info.ImageUnique);
				dbManager.AddParameters(39, "@pos_type_seq", Info.Position == null  ? null : Info.Position.PosTypeSeq);

                strSQL = "UPDATE HRM_PS_EMPLOYEES"
                    + " SET NAME_PREFIX_SEQ = @name_prefix_seq"
                        + ", FIRST_NAME = @first_name"
                        + ", LAST_NAME = @last_name"
                        + ", FIRST_NAME_ENG = @first_name_eng"
                        + ", LAST_NAME_ENG = @last_name_eng"
                        + ", NICK_NAME = @nick_name"
                        + ", GENDER_SEQ = @gender_seq"
                        //+ ", CODE_SCAN = @code_scan"
                        + ", RELIGION_SEQ = @religion_seq"
                        + ", RACE_NAT_SEQ = @race_nat_seq"
                        + ", NAT_SEQ = @nat_seq"
                        + ", BIRTH_DATE = @birth_date"
                        + ", EMP_TYPE_SEQ = @emp_type_seq"
                        + ", CLASS_SEQ = @class_seq"
                        + ", SALARY_LEVEL_SEQ = @salary_level_seq"
                        + ", SALARY = @salary"
                        + ", POS_SEQ = @pos_seq"
                        + ", WORKING_JOB_SITE_SEQ = @working_job_site_seq"
                        + ", DATE_ADMITTED = @date_admitted"
                        + ", DATE_WORK_START = @date_work_start"
                        + ", DATE_QUIT = @date_quit"
                        + ", EMP_STATUS_SEQ = @emp_status_seq"
                        //+ ", EMP_SEC_SEQ = @emp_sec_seq"
                        + ", CITIZEN = @citizen"
                        + ", TAX_PAYER = @tax_payer"
                        + ", SOCIAL = @social"
                        + ", OFFICIAL = @official"
                        + ", PROBATION_DATE_END = @probation_date_end"
                        + ", BLOOD_GROUP_SEQ = @blood_group"
                        + ", DEATH_DATE = @death_date"
                        + ", DATE_LATEST_POS = @date_latest_pos"
                        + ", DATE_LATEST_CLASS = @date_latest_class"
                        + ", DATE_LATEST_WORKING_JOB_SITE = @date_latest_working_job_site"
                        //+ ", FUND_MEMBER = @fund_member"
                        + ", MAR_STAT_SEQ = @mar_stat_seq"
                        + ", UPDATED_BY = @updated_by"
                        + ", UPDATED_WHEN = @updated_when"
                        + ", image_unique = @image_unique"
                    + " WHERE EMP_SEQ = @emp_seq;";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch (Exception ex)
            {
                dbManager.RollBackTransaction();
                throw ex;
            }
            finally
            {
                dbManager.Close(); 
                dbManager.Dispose();
            }
            if (Info.Position != null && Info.Position.PositionType != null)
            {
                UpdateFieldPositionType(Info.Sequence, Info.Position.PositionType.Sequence);
            }
            if (Info.Position != null)
            {
                UpdateFieldExecPositionSeq(Info.Position.Sequence, Info.Position.Executive == null ? null : Info.Position.Executive.Sequence);
            }
        }

        public void UpdateSingleField(int Sequence, string fieldName, object fieldValue, int updateBy)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@Sequence", Sequence);
                dbManager.AddParameters(1, "@fieldValue", fieldValue);
                dbManager.AddParameters(2, "@updateBy", updateBy); 

                strSQL = string.Format("UPDATE HRM_PS_EMPLOYEES"
                    + " SET {0} = @fieldValue" 
                        + ", UPDATED_BY = @updateBy"
                        + ", UPDATED_WHEN = GETDATE()"
                    + " WHERE EMP_SEQ = @Sequence;", fieldName);

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Close();
                dbManager.Dispose();
            } 
        }

        public int? GetFieldPositionSequence(int employeeSequence)
        {
            int? fieldPosSeq = null;
            try
            {
                // Find Field Pos from Employee Sequence
                {
                    dbManager.Open();
                    dbManager.CreateParameters(2);

                    dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                    dbManager.AddParameters(1, "@emp_seq", employeeSequence);

                    string strSQL = @"SELECT f.field_pos_seq, e.emp_seq, e.first_name 
                                    FROM HRM_PS_EMPLOYEES e 
                                    INNER JOIN HRM_MP_POSITIONS p ON e.pos_seq = p.pos_seq
                                    INNER JOIN HRM_MP_FIELD_POSITIONS f ON p.FIELD_POS_SEQ = f.FIELD_POS_SEQ 
                                    WHERE   e.emp_seq = @emp_seq
                                            and e.record_status = @ActiveRecord 
                                            and p.record_status = @ActiveRecord
                                            and f.record_status = @ActiveRecord";
                    DataRowCollection drs = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;

                    if (drs.Count > 0 && (drs[0]["field_pos_seq"] as int?).HasValue)
                    {
                        fieldPosSeq = (int)drs[0]["field_pos_seq"];
                    }
                }
            }
            catch (Exception error)
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
            return fieldPosSeq;
        }

        public void UpdateFieldPositionType(int employeeSequence, int positionTypeSeq)
        {
            try
            {
                int? fieldPosSeq = GetFieldPositionSequence(employeeSequence);
                dbManager.Open();
                // Update Field Pos
                if (fieldPosSeq.HasValue)
                {
                    dbManager.CreateParameters(3);

                    dbManager.AddParameters(0, "@ActiveRecord", SystemInfo._ActiveRecordStatus);
                    dbManager.AddParameters(1, "@field_pos_seq", fieldPosSeq);
                    dbManager.AddParameters(2, "@pos_type_seq", positionTypeSeq);

                    string strSQL = @" UPDATE HRM_MP_FIELD_POSITIONS
                                         SET pos_type_seq = @pos_type_seq
                                         WHERE FIELD_POS_SEQ = @field_pos_seq
                                         AND record_status = @ActiveRecord";
                    dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                }

            }
            catch (Exception error)
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }

        }

        public void UpdateFieldExecPositionSeq(int? posSeq, int? execPosSeq)
        {
            try
            {
                dbManager.Open();
                // Update Field Pos
                if (posSeq.HasValue)
                {
                    dbManager.CreateParameters(2);

                    dbManager.AddParameters(0, "@pos_seq", posSeq);
                    dbManager.AddParameters(1, "@exec_pos_seq", execPosSeq);

                    const string strSql = @"  UPDATE hrm_mp_positions
                                            SET exec_pos_seq = @exec_pos_seq
                                            WHERE pos_seq = @pos_seq;";
                    dbManager.ExecuteNonQuery(CommandType.Text, strSql);
                }
            }
            catch (Exception error)
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }

        }

        public void UpdatePersonal(EmployeeInfo Info)
        {
            string strSQL;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(21);

                dbManager.AddParameters(0, "@emp_seq", Info.Sequence);
                dbManager.AddParameters(1, "@name_prefix_seq", Info.Person.NamePrefix == null ? (int?)null : Info.Person.NamePrefix.Sequence);
                dbManager.AddParameters(2, "@first_name", Info.Person.FirstName);
                dbManager.AddParameters(3, "@last_name", Info.Person.LastName);
                dbManager.AddParameters(4, "@first_name_eng", Info.Person.FirstNameEng);
                dbManager.AddParameters(5, "@last_name_eng", Info.Person.LastNameEng);
                dbManager.AddParameters(6, "@gender_seq", Info.Person.Gender == null ? (int?)null : Info.Person.Gender.Sequence);
                dbManager.AddParameters(7, "@religion_seq", Info.Person.Religion == null ? (int?)null : Info.Person.Religion.Sequence);
                dbManager.AddParameters(8, "@race_nat_seq", Info.Person.RaceNationalities == null ? (int?)null : Info.Person.RaceNationalities.Sequence);
                dbManager.AddParameters(9, "@nat_seq", Info.Person.Nationalities == null );
                dbManager.AddParameters(10, "@birth_date", Info.Person.BirthDate);
                dbManager.AddParameters(11, "@citizen", Info.Person.Citizen);
                dbManager.AddParameters(12, "@tax_payer", Info.Person.TaxPayer);
                dbManager.AddParameters(13, "@social", Info.Person.Social);
                dbManager.AddParameters(14, "@blood_group", Info.Person.BloodGroup == null ? (int?)null : Info.Person.BloodGroup.Sequence);
                dbManager.AddParameters(15, "@emp_status_seq", Info.Status == null ? (int?)null : Info.Status.Sequence);
                dbManager.AddParameters(16, "@death_date", Info.Person.DeathDate);
                dbManager.AddParameters(17, "@nick_name", Info.Person.NickName);
                dbManager.AddParameters(18, "@mar_stat_seq", Info.Person.MaritalStatus.Sequence);
                dbManager.AddParameters(19, "@updated_by", Info.UpdatedBy);
                dbManager.AddParameters(20, "@updated_when", DateTime.Today);

                strSQL = "UPDATE HRM_PS_EMPLOYEES"
                    + " SET NAME_PREFIX_SEQ = @name_prefix_seq"
                        + ", FIRST_NAME = @first_name"
                        + ", LAST_NAME = @last_name"
                        + ", FIRST_NAME_ENG = @first_name_eng"
                        + ", LAST_NAME_ENG = @last_name_eng"
                        + ", NICK_NAME = @nick_name"
                        + ", GENDER_SEQ = @gender_seq"
                        + ", RELIGION_SEQ = @religion_seq"
                        + ", RACE_NAT_SEQ = @race_nat_seq"
                        + ", NAT_SEQ = @nat_seq"
                        + ", BIRTH_DATE = @birth_date"
                        + ", EMP_STATUS_SEQ = @emp_status_seq"
                        + ", CITIZEN = @citizen"
                        + ", TAX_PAYER = @tax_payer"
                        + ", SOCIAL = @social"
                    //+ ", probation_date_end = @probation_date_end"
                        + ", BLOOD_GROUP_SEQ = @blood_group"
                        + ", MAR_STAT_SEQ = @mar_stat_seq"
                        + ", DEATH_DATE = @death_date"
                        + ", UPDATED_BY = @updated_by"
                        + ", UPDATED_WHEN = @updated_when"
                    + " WHERE EMP_SEQ = @emp_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.Close(); 
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Close(); 
                dbManager.Dispose();
            }
        }

        public void Delete(int Sequence, int UpdatedBy)
        {
            string strSQL;

            try
            {

                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@Seq", Sequence);
                dbManager.AddParameters(1, "@RecordStatus", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@UpdatedBy", UpdatedBy);
                dbManager.AddParameters(3, "@UpdatedWhen", DateTime.Today);

                strSQL = "UPDATE HRM_PS_EMPLOYEES"
                    + " SET RECORD_STATUS = @RecordStatus"
                        + ", UPDATED_BY = @UpdatedBy"
                        + ", UPDATED_WHEN = @UpdatedWhen"
                    + " WHERE EMP_SEQ = @Seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
                dbManager.Close(); 
                dbManager.CommitTransaction();
            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Close(); 
                dbManager.Dispose();
            }
        }
        public void UploadFile(ImageInfo image_info, string image_id)
        {
            if (Convert.ToInt64(image_info.File_info.Length) > 0)
            {
                if (!Directory.Exists(image_info.Dest_path))
                {
                    Directory.CreateDirectory(image_info.Dest_path);
                }

                if (File.Exists(image_info.Dest_path + image_id + image_info.File_info.Extension))
                {
                    File.Delete(image_info.Dest_path + image_id + image_info.File_info.Extension);
                }

                File.Copy(image_info.File_info.FullName, image_info.Dest_path + image_id + image_info.File_info.Extension);
            }
        }

        public void UploadFileToServer(ImageInfo image_info, string image_id)
        {
            string ftpServer = @"ftp://10.8.99.73/APP_UI/Image/ItemPicture";
            string userName = "Administrator";
            string password = "P@ssw0rd";
            using (System.Net.WebClient client = new System.Net.WebClient())
            {
                client.Credentials = new System.Net.NetworkCredential(userName, password);
                client.UploadFile(ftpServer + "/" + image_id + image_info.File_info.Extension, image_info.File_info.FullName);
            }
        }


        public string GetGenderSeq(string NamePrefixSeq)
        {
            string strSQL = "";
            string GenderSeq;

            try
            {
                dbManager.Open();
                dbManager.BeginTransaction();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@name_prefix_seq", NamePrefixSeq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT gender_seq FROM MAS_NAME_PREFIXES WHERE RECORD_STATUS not like @record_status AND name_prefix_seq = @name_prefix_seq";

                GenderSeq = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0][0].ToString();

                dbManager.CommitTransaction();

            }
            catch
            {
                dbManager.RollBackTransaction();
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }

            return GenderSeq;
        }


        //public EmployeeInfo GetEmployeeInfo(Nullable<int> seq)
        //{
        //    EmployeeInfo employeeInfo = new EmployeeInfo();
        //    string strSQL;

        //    try
        //    {
        //        dbManager.Open();
        //        dbManager.CreateParameters(2);

        //        dbManager.AddParameters(0, "@seq", seq);
        //        dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

        //        strSQL = "SELECT e.EMP_SEQ, e.NAME_PREFIX_SEQ, np.PREFIX_NAME, e.FIRST_NAME, e.LAST_NAME"
        //                + ", e.FIRST_NAME_ENG, e.LAST_NAME_ENG"
        //                + ", CONCAT(np.PREFIX_NAME, e.FIRST_NAME, ' ', e.LAST_NAME) AS EMP_NAME"
        //                + ", CONCAT(np.PREFIX_NAME_ENG, e.FIRST_NAME_ENG, ' ', e.LAST_NAME_ENG) AS EMP_NAME_ENG"
        //                + ", e.GENDER_SEQ, e.RELIGION_SEQ, e.RACE_NAT_SEQ, e.NAT_SEQ, e.BIRTH_DATE, e.EMP_TYPE_SEQ"
        //                + ", e.CLASS_SEQ, e.SALARY_LEVEL_SEQ, e.SALARY, e.POS_SEQ, e.WORKING_JOB_SITE_SEQ"
        //                + ", e.DATE_ADMITTED, e.DATE_WORK_START, e.DATE_QUIT, e.EMP_STATUS_SEQ, e.EMP_SEC_SEQ"
        //                + ", e.CITIZEN, e.TAX_PAYER , e.SOCIAL, e.OFFICIAL, e.BLOOD_GROUP, e.PROBATION_DATE_END, e.DEATH_DATE"
        //                + ", e.DATE_LATEST_POS, e.DATE_LATEST_CLASS, e.DATE_LATEST_WORKING_JOB_SITE, e.FUND_MEMBER"
        //                + ", c.CLASS_NAME, wj.JOB_SITE_NAME AS WORKING_JOB_SITE_NAME, et.EMP_TYPE_NAME"
        //                + ", gen.GENDER_NAME, re.RELIGION_NAME, rnat.NAT_NAME AS RACE_NAT_NAME, nat.NAT_NAME"
        //                + ", es.EMP_STATUS_NAME"
        //           + " FROM HRM_PS_EMPLOYEES e"
        //                + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
        //                + " LEFT JOIN HRM_PS_EMPLOYEE_TYPES et ON e.EMP_TYPE_SEQ = et.EMP_TYPE_SEQ"
        //                + " LEFT JOIN HRM_MP_CLASSES c ON e.CLASS_SEQ = c.CLASS_SEQ"
        //                + " LEFT JOIN HRM_MP_POSITIONS p ON e.POS_SEQ = p.POS_SEQ"
        //                + " LEFT JOIN HRM_MP_JOB_SITES pj ON p.JOB_SITE_SEQ = pj.JOB_SITE_SEQ"
        //                + " LEFT JOIN HRM_MP_JOB_SITES wj ON e.WORKING_JOB_SITE_SEQ = wj.JOB_SITE_SEQ"
        //                + " LEFT JOIN HRM_PS_EMPLOYEE_STATUSES es ON e.EMP_STATUS_SEQ = es.EMP_STATUS_SEQ"
        //                + " LEFT JOIN MAS_GENDERS gen ON e.GENDER_SEQ = gen.GENDER_SEQ"
        //                + " LEFT JOIN MAS_RELIGIONS re ON e.RELIGION_SEQ = re.RELIGION_SEQ"
        //                + " LEFT JOIN MAS_NATIONALITIES rnat ON e.RACE_NAT_SEQ = rnat.NAT_SEQ"
        //                + " LEFT JOIN MAS_NATIONALITIES nat ON e.NAT_SEQ = nat.NAT_SEQ"
        //           + " WHERE e.EMP_SEQ = @seq";

        //        IDataReader rdr = dbManager.ExecuteReader(CommandType.Text, strSQL);

        //        while (rdr.Read())
        //        {
        //            employeeInfo.Seq = rdr.GetInt32(0);
        //            employeeInfo.NamePrefixSeq = rdr.IsDBNull(1) ? (int?)null : rdr.GetInt32(1);
        //            employeeInfo.PrefixName = rdr.IsDBNull(2) ? "" : rdr.GetString(2);
        //            employeeInfo.FirstName = rdr.IsDBNull(3) ? "" : rdr.GetString(3);
        //            employeeInfo.LastName = rdr.IsDBNull(4) ? "" : rdr.GetString(4);
        //            employeeInfo.FirstNameEng = rdr.IsDBNull(5) ? "" : rdr.GetString(5);
        //            employeeInfo.LastNameEng = rdr.IsDBNull(6) ? "" : rdr.GetString(6);
        //            employeeInfo.EmpName = rdr.IsDBNull(7) ? "" : rdr.GetString(7);
        //            employeeInfo.EmpNameEng = rdr.IsDBNull(8) ? "" : rdr.GetString(8);
        //            employeeInfo.GenderSeq = rdr.IsDBNull(9) ? (int?)null : rdr.GetInt32(9);
        //            employeeInfo.ReligionSeq = rdr.IsDBNull(10) ? (int?)null : rdr.GetInt32(10);
        //            employeeInfo.RaceNatSeq = rdr.IsDBNull(11) ? (int?)null : rdr.GetInt32(11);
        //            employeeInfo.NatSeq = rdr.IsDBNull(12) ? (int?)null : rdr.GetInt32(12);
        //            employeeInfo.BirthDate = rdr.IsDBNull(13) ? (DateTime?)null : rdr.GetDateTime(13);
        //            employeeInfo.EmpTypeSeq = rdr.IsDBNull(14) ? (int?)null : rdr.GetInt32(14);
        //            employeeInfo.ClassSeq = rdr.IsDBNull(15) ? (int?)null : rdr.GetInt32(15);
        //            employeeInfo.SalaryLevelSeq = rdr.IsDBNull(16) ? (int?)null : rdr.GetInt32(16);
        //            employeeInfo.Salary = rdr.IsDBNull(17) ? (double?)null : rdr.GetDouble(17);
        //            employeeInfo.PositionSeq = rdr.IsDBNull(18) ? (int?)null : rdr.GetInt32(18);
        //            employeeInfo.WorkingJobSiteSeq = rdr.IsDBNull(19) ? (int?)null : rdr.GetInt32(19);
        //            employeeInfo.AdmittedDate = rdr.IsDBNull(20) ? (DateTime?)null : rdr.GetDateTime(20);
        //            employeeInfo.WorkStartDate = rdr.IsDBNull(21) ? (DateTime?)null : rdr.GetDateTime(21);
        //            employeeInfo.QuitDate = rdr.IsDBNull(22) ? (DateTime?)null : rdr.GetDateTime(22);
        //            employeeInfo.EmpStatusSeq = rdr.IsDBNull(23) ? (int?)null : rdr.GetInt32(23);
        //            employeeInfo.EmpSectionSeq = rdr.IsDBNull(24) ? (int?)null : rdr.GetInt32(24);
        //            employeeInfo.Citizen = rdr.IsDBNull(25) ? "" : rdr.GetString(25);
        //            employeeInfo.TaxPayer = rdr.IsDBNull(26) ? "" : rdr.GetString(26);
        //            employeeInfo.Social = rdr.IsDBNull(27) ? "" : rdr.GetString(27);
        //            employeeInfo.Official = rdr.IsDBNull(28) ? "" : rdr.GetString(28);
        //            employeeInfo.BloodGroup = rdr.IsDBNull(29) ? "" : rdr.GetString(29);
        //            employeeInfo.ProbationEndDate = rdr.IsDBNull(29) ? (DateTime?)null : rdr.GetDateTime(30);
        //            employeeInfo.DeathDate = rdr.IsDBNull(31) ? (DateTime?)null : rdr.GetDateTime(31);
        //            employeeInfo.LatestPositionDate = rdr.IsDBNull(32) ? (DateTime?)null : rdr.GetDateTime(32);
        //            employeeInfo.LatestClassDate = rdr.IsDBNull(33) ? (DateTime?)null : rdr.GetDateTime(33);
        //            employeeInfo.LatestWorkingJobSiteDate = rdr.IsDBNull(34) ? (DateTime?)null : rdr.GetDateTime(34);
        //            employeeInfo.FundMember = rdr.IsDBNull(35) ? (int?)null : rdr.GetInt32(35);
        //            employeeInfo.ClassName = rdr.IsDBNull(36) ? "" : rdr.GetString(36);
        //            employeeInfo.WorkingJobSiteName = rdr.IsDBNull(37) ? "" : rdr.GetString(37);
        //            employeeInfo.EmpTypeName = rdr.IsDBNull(38) ? "" : rdr.GetString(38);
        //            employeeInfo.GenderName = rdr.IsDBNull(39) ? "" : rdr.GetString(39);
        //            employeeInfo.ReligionName = rdr.IsDBNull(40) ? "" : rdr.GetString(40);
        //            employeeInfo.RaceNatName = rdr.IsDBNull(41) ? "" : rdr.GetString(41);
        //            employeeInfo.NatName = rdr.IsDBNull(42) ? "" : rdr.GetString(42);
        //            employeeInfo.EmpStatusName = rdr.IsDBNull(43) ? "" : rdr.GetString(43);
        //        }

        //        return employeeInfo;
        //    }
        //    catch
        //    {
        //        return null;
        //        throw;
        //    }
        //    finally
        //    {
        //        dbManager.CloseReader();
        //        dbManager.Dispose();
        //    }
        //}
        //public IList<EmployeeInfo> LoadGridEmployeeInfo(Nullable<int> EmpTypeSeq)
        //{
        //    string strSQL;
        //    DataSet ds = null;
        //    DataRowCollection dr = null;
        //    IList<EmployeeInfo> infoList = new List<EmployeeInfo>();

        //    try
        //    {
        //        dbManager.Open();
        //        dbManager.CreateParameters(2);

        //        dbManager.AddParameters(0, "@EmpTypeSeq", EmpTypeSeq);
        //        dbManager.AddParameters(1, "@DeleteRecord", SystemInfo._DeleteRecordStatus);

        //        strSQL = "SELECT e.EMP_SEQ, e.NAME_PREFIX_SEQ, np.PREFIX_NAME, e.FIRST_NAME, e.LAST_NAME"
        //                 + ", e.FIRST_NAME_ENG, e.LAST_NAME_ENG"
        //                 + ", CONCAT(np.PREFIX_NAME, e.FIRST_NAME, ' ', e.LAST_NAME) AS EMP_NAME"
        //                 + ", CONCAT(np.PREFIX_NAME_ENG, e.FIRST_NAME_ENG, ' ', e.LAST_NAME_ENG) AS EMP_NAME_ENG"
        //                 + ", e.GENDER_SEQ, e.RELIGION_SEQ, e.RACE_NAT_SEQ, e.NAT_SEQ, e.BIRTH_DATE, e.EMP_TYPE_SEQ"
        //                 + ", e.CLASS_SEQ, e.SALARY_LEVEL_SEQ, e.SALARY, e.POS_SEQ, e.WORKING_JOB_SITE_SEQ"
        //                 + ", e.DATE_ADMITTED, e.DATE_WORK_START, e.DATE_QUIT, e.EMP_STATUS_SEQ, e.EMP_SEC_SEQ"
        //                 + ", e.CITIZEN, e.TAX_PAYER , e.SOCIAL, e.OFFICIAL, e.BLOOD_GROUP, e.PROBATION_DATE_END, e.DEATH_DATE"
        //                 + ", e.DATE_LATEST_POS, e.DATE_LATEST_CLASS, e.DATE_LATEST_WORKING_JOB_SITE, e.FUND_MEMBER"
        //                 + ", c.CLASS_NAME, wj.JOB_SITE_NAME AS WORKING_JOB_SITE_NAME, et.EMP_TYPE_NAME"
        //                 + ", p.POS_REF, p.POS_NAME"
        //            + " FROM HRM_PS_EMPLOYEES e"
        //                 + " LEFT JOIN MAS_NAME_PREFIXES np ON e.NAME_PREFIX_SEQ = np.NAME_PREFIX_SEQ"
        //                 + " LEFT JOIN HRM_PS_EMPLOYEE_TYPES et ON e.EMP_TYPE_SEQ = et.EMP_TYPE_SEQ"
        //                 + " LEFT JOIN HRM_MP_CLASSES c ON e.CLASS_SEQ = c.CLASS_SEQ"
        //                 + " LEFT JOIN HRM_MP_POSITIONS p ON e.POS_SEQ = p.POS_SEQ"
        //                 + " LEFT JOIN HRM_MP_JOB_SITES pj ON p.JOB_SITE_SEQ = pj.JOB_SITE_SEQ"
        //                 + " LEFT JOIN HRM_MP_JOB_SITES wj ON e.WORKING_JOB_SITE_SEQ = wj.JOB_SITE_SEQ"
        //                 + " LEFT JOIN HRM_PS_EMPLOYEE_STATUSES es ON e.EMP_STATUS_SEQ = es.EMP_STATUS_SEQ"
        //            + " WHERE e.RECORD_STATUS not like @DeleteRecord"
        //                + " AND e.EMP_TYPE_SEQ = @EmpTypeSeq";

        //        ds = dbManager.ExecuteDataSet(CommandType.Text, strSQL);
        //        dr = ds.Tables[0].Rows;
        //        if (dr.Count > 0)
        //        {
        //            for (int i = 0; i < dr.Count; i++)
        //            {
        //                EmployeeInfo info = new EmployeeInfo();

        //                info.Seq = Convert.IsDBNull(dr[i]["EMP_SEQ"]) ? (int?)null : Int32.Parse(dr[i]["EMP_SEQ"].ToString());
        //                info.PrefixName = Convert.IsDBNull(dr[i]["PREFIX_NAME"]) ? "" : (dr[i]["PREFIX_NAME"]).ToString();
        //                info.FirstName = Convert.IsDBNull(dr[i]["FIRST_NAME"]) ? "" : (dr[i]["FIRST_NAME"]).ToString();
        //                info.LastName = Convert.IsDBNull(dr[i]["LAST_NAME"]) ? "" : (dr[i]["LAST_NAME"]).ToString();
        //                info.FirstNameEng = Convert.IsDBNull(dr[i]["FIRST_NAME_ENG"]) ? "" : (dr[i]["FIRST_NAME_ENG"]).ToString();
        //                info.LastNameEng = Convert.IsDBNull(dr[i]["LAST_NAME_ENG"]) ? "" : (dr[i]["LAST_NAME_ENG"]).ToString();
        //                info.EmpName = Convert.IsDBNull(dr[i]["EMP_NAME"]) ? "" : (dr[i]["EMP_NAME"]).ToString();
        //                info.EmpNameEng = Convert.IsDBNull(dr[i]["EMP_NAME_ENG"]) ? "" : (dr[i]["EMP_NAME_ENG"]).ToString();
        //                info.GenderSeq = Convert.IsDBNull(dr[i]["GENDER_SEQ"]) ? (int?)null : Int32.Parse(dr[i]["GENDER_SEQ"].ToString());
        //                info.ReligionSeq = Convert.IsDBNull(dr[i]["RELIGION_SEQ"]) ? (int?)null : Int32.Parse(dr[i]["RELIGION_SEQ"].ToString());
        //                info.RaceNatSeq = Convert.IsDBNull(dr[i]["RACE_NAT_SEQ"]) ? (int?)null : Int32.Parse(dr[i]["RACE_NAT_SEQ"].ToString());
        //                info.NatSeq = Convert.IsDBNull(dr[i]["NAT_SEQ"]) ? (int?)null : Int32.Parse(dr[i]["NAT_SEQ"].ToString());
        //                info.BirthDate = Convert.IsDBNull(dr[i]["BIRTH_DATE"]) ? (DateTime?)null : DateTime.Parse(dr[i]["BIRTH_DATE"].ToString());
        //                info.EmpTypeSeq = Convert.IsDBNull(dr[i]["EMP_TYPE_SEQ"]) ? (int?)null : Int32.Parse(dr[i]["EMP_TYPE_SEQ"].ToString());
        //                info.ClassSeq = Convert.IsDBNull(dr[i]["CLASS_SEQ"]) ? (int?)null : Int32.Parse(dr[i]["CLASS_SEQ"].ToString());
        //                info.SalaryLevelSeq = Convert.IsDBNull(dr[i]["SALARY_LEVEL_SEQ"]) ? (int?)null : Int32.Parse(dr[i]["SALARY_LEVEL_SEQ"].ToString());
        //                info.Salary = Convert.IsDBNull(dr[i]["SALARY"]) ? (int?)null : Int32.Parse(dr[i]["SALARY"].ToString());
        //                info.PositionSeq = Convert.IsDBNull(dr[i]["POS_SEQ"]) ? (int?)null : Int32.Parse(dr[i]["POS_SEQ"].ToString());
        //                info.WorkingJobSiteSeq = Convert.IsDBNull(dr[i]["WORKING_JOB_SITE_SEQ"]) ? (int?)null : Int32.Parse(dr[i]["WORKING_JOB_SITE_SEQ"].ToString());
        //                info.AdmittedDate = Convert.IsDBNull(dr[i]["DATE_ADMITTED"]) ? (DateTime?)null : DateTime.Parse(dr[i]["DATE_ADMITTED"].ToString());
        //                info.WorkStartDate = Convert.IsDBNull(dr[i]["DATE_WORK_START"]) ? (DateTime?)null : DateTime.Parse(dr[i]["DATE_WORK_START"].ToString());
        //                info.QuitDate = Convert.IsDBNull(dr[i]["DATE_QUIT"]) ? (DateTime?)null : DateTime.Parse(dr[i]["DATE_QUIT"].ToString());
        //                info.EmpStatusSeq = Convert.IsDBNull(dr[i]["EMP_STATUS_SEQ"]) ? (int?)null : Int32.Parse(dr[i]["EMP_STATUS_SEQ"].ToString());
        //                info.EmpSectionSeq = Convert.IsDBNull(dr[i]["EMP_SEC_SEQ"]) ? (int?)null : Int32.Parse(dr[i]["EMP_SEC_SEQ"].ToString());
        //                info.Citizen = Convert.IsDBNull(dr[i]["CITIZEN"]) ? "" : (dr[i]["CITIZEN"]).ToString();
        //                info.ClassName = Convert.IsDBNull(dr[i]["CLASS_NAME"]) ? "" : (dr[i]["CLASS_NAME"]).ToString();
        //                info.WorkingJobSiteName = Convert.IsDBNull(dr[i]["WORKING_JOB_SITE_NAME"]) ? "" : (dr[i]["WORKING_JOB_SITE_NAME"]).ToString();
        //                info.EmpTypeName = Convert.IsDBNull(dr[i]["EMP_TYPE_NAME"]) ? "" : (dr[i]["EMP_TYPE_NAME"]).ToString();
        //                info.PositionRefNo = Convert.IsDBNull(dr[i]["POS_REF"]) ? "" : (dr[i]["POS_REF"]).ToString();
        //                info.PositionName = Convert.IsDBNull(dr[i]["POS_NAME"]) ? "" : (dr[i]["POS_NAME"]).ToString();
        //                infoList.Add(info);
        //            }
        //        }
        //    }
        //    catch (Exception ex)
        //    {
        //        throw (ex);
        //    }
        //    return infoList;
        //}    
        //public DataTable SelectGrid(string emp_type_seq, string emp_status_seq, string active)
        //{
        //    return SelectGrid(emp_type_seq, emp_status_seq, active, "");
        //}
    }
}
